[Power BI Tips] Power Query で動的に変わる列名に対応するには？ - 01

はじめに - 前提

データソースにはいろんな種類があるもので、データのカタチも多種多様ですよね。すべてのデータがテーブル形式に整っているわけではありません。中には、列定義がコロコロと変わることもあります。

今回は列定義の中でももっともポピュラーなもの、列数が動的に変わり、列名もちゃんと名付けたいというケースを考えてみます。

それではスタート

今回は Power Query でこのテーマを扱ってみます。

用意するデータ

説明に使うデータは Excel で用意します。ひとつの Excel ファイルに2つのシートを用意して、それぞれのシートに以下の様なテーブルをひとつずつ用意します。

Dataシート

Data シートに データ というテーブル名で Col1, Col2, Col3 と3列のテーブルを作りました。

ColumnDefinitionシート

ColumnDefinition シートに 列定義 というテーブル名で、元の列名と置き換えたい列名を定義しました。

ご覧になってやりたいことが想像ついた方もいらっしゃるかもしれません。そう、データ の列名が Col1 - Col3 となっているので、これを 列定義 テーブルの内容で列名を置き換えたいということです。つまり、置き換えた後は以下の様にしたいわけです。

置換後のイメージ

ただし Excel 上ではなく、Power BI Desktop で読み込んで、Power Query で処理をします。
（Excel 上の Power Query でも可能ですが、ワタクシ、Power BI の人なので、Power BI Desktop を使用します）

Power BI Desktop で読み込む

さて、データが準備出来たら、Power BI Desktop で読み込みましょう。(Power BI Desktop の細かい操作は記載しません。あしからず)
用意した Excel ファイルを読み込みます。

2つのテーブルを読み込むと、クエリが2つできてるはずです。

「データ」テーブル

こちらが「データ」テーブルですね。

「列定義」テーブル

そしてこちらが「列定義」テーブルです。

ここから、「列定義」テーブルの内容で、「データ」テーブルの列名を変更していきたいと思います。

なぜ手動で列名を変更しないの？

その前に、ひとつ説明をしておきます。
これを読まれている方の方の中には

「列名って、手動で変更できなかったっけ？」

と思われた方もいると思います。

はい、もちろんできます。列をダブルクリックするか、右クリックから [名前の変更] で列名を変更することが可能です。

試しに変更してみるとこうなります。
もちろんこれでも OK なのですが、ひとつ困る場合があります。
数式バーを見ると

名前が変更された列

= Table.RenameColumns( 変更された型, { { "Col1", "ID" }, { "Col2", "日付" }, { "Col3", "値" } } )

ご覧になってわかるように、この方法だと、列名を文字列で指定することになります。データ更新をした場合に、データソースの列定義が変わらなければ、手動での列名変更でも問題ありません。しかし、今回は動的に列名が変わる場合にどうするか？というのがテーマです。なので、これだと、列名が変わった場合に困っちゃうのです。

例えば、

1. Col4 が追加されたら
2. Col3 が Col3.1 に変わったら

1の場合は、エラーは起きませんが、COl4 は無視されます。なぜなら、Power Query で指定をされていませんから、知らない列になってしまうわけです。結果、Col4 という名前の列が出力されてしまいます。2の場合は、指定された Col3 がいないので、エラーが発生します。

このような変化が起こる可能性があるかもしれない、とわかっている場合が今回のテーマです。

詳細エディターで Power Query を変更する

Power Query を手動で変更することになるのですが、テーブルの列名を変更するための関数を確認しておきましょう。

さきほど手動で列名を変更した際にも使われていますが、Table.RenameColumns 関数です。
その定義は

Table.RenameColumns(table as table, renames as list, optional missingField as nullable number) as table

となっています。第1引数がテーブル、第2引数がリスト、第3引数はオプションでリストに一致する列名が存在しない場合にどうするかを指定することができます。
通常、第1引数は前のステップ名を指定します。問題は第2引数です。これは先ほど手動で変更した際に自動生成された { { "Col1", "ID" }, { "Col2", "日付" }, { "Col3", "値" } } が参考になります。

{} はリストを表しますが、リストの中に元の列名と新たな列名のペアをリストで含めていることがわかります。つまり、「列定義」テーブルをこの形にすればいいのです。

とは言っても、テーブルをこのようなリストのカタチにできるの？って思いますよね。できます。Table.ToRows 関数を使いましょう。

Table.ToRows(table as table) as list 

その定義を見ると、テーブルを引数に渡してあげると意図するリストにしてくれることがわかります。

[列定義] クエリを変更する

と、いうわけで、[列定義] クエリを詳細エディターで開いて、以下の様に変更します。

let
    ソース = Excel.Workbook(File.Contents("ファイルの場所"), null, true),
    列定義_Table = ソース{[Item="列定義",Kind="Table"]}[Data],
    変更された型 = Table.TransformColumnTypes(列定義_Table,{{"元の列名", type text}, {"置き換えたい列名", type text}}),
    リスト作成 = Table.ToRows(変更された型)
in
    リスト作成

※Excel ファイルの場所はご自身の環境に合わせて変更してください

画像の5行目を追加し、in 句の後ろを リスト作成 に変更しました。そうするとこんな感じに表示されます。

これで OK です。

[データ] クエリを変更する

次は**[データ]** クエリです。列定義のリストはできたので、Table.RenameColumns 関数を使って、列名を変更しましょう。

let
    ソース = Excel.Workbook(File.Contents("Excelファイルの場所"), null, true),
    データ_Table = ソース{[Item="データ",Kind="Table"]}[Data],
    変更された型 = 
        Table.TransformColumnTypes(
            データ_Table,
            {
                {"Col1", Int64.Type}, 
                {"Col2", type date}, 
                {"Col3", Int64.Type}
            }
        ),
    列名変更 = 
        Table.RenameColumns(
            変更された型,
            列定義
        )
in
    列名変更

※Excel ファイルの場所はご自身の環境に合わせて変更してください

13行目から17行目が追加したステップですが、Table.RenameColumns の第1引数に直前のステップ名「変更された型」、第2引数は変更したい列名と新しい列名のペアをリストで出力する [列定義] クエリを指定してます。

こうすることで、画像のようにきれいに列名が変更されたはずです。
ここまでできたら、一度 [閉じて適用] をしてください。

Excel ファイルで列定義を変更してみる

Power Query としては以上で終了なのですが、本当に列定義が変わっても正常に読み込んでくれるのか、試してみましょう。

Excelファイルを開いてください。

「列定義」テーブルに Col4, 備考 を追加します。

「データ」テーブルに Col4 列を追加して、値を適当に入れてください。

これで、備考を表す4列目が追加され、テーブルの列定義が変更されたことになります。Excel ファイルを保存して閉じ、Power BI Desktop に戻りましょう。
テーブルに3列しかないことを確認して、[更新] ボタンをクリックします。

↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

いかがでしょうか？ちゃんと4列になって、[備考] 列が追加されたでしょう。
もしできなかったら、落ち着いて、最初から確認してみてください。

おわり

いかがでしたでしょうか？
こんな感じに変更に強い Power Query を考えてみるのも、とてもいい勉強になります。

Power Query エディターでマウス操作のみでポチポチすると、すべて固定の列名で処理が自動生成されます。文字列で定義名を指定することを 名前解決 と言いいますが、固定の文字列で処理が記述されてしまうと、変更に弱いものになってしまいます。場合によっては、動的な変化に対応する処理が求められることもありますので、今回のように動的変化に対応する方法を知っておくと、例えばテンプレートファイル作成する場合にはとても有効です。

ぜひ知っておいてください。
最後まで読んでいただきありがとうございました。次回はこの続編で今回の考え方を応用して、昨今増えている動的に変化する JSON データへの対応を考えてみます。

↓↓↓↓↓↓ 続編 ↓↓↓↓↓↓

また皆様からのリクエストもお待ちしております。

何かリクエストがあれば、以下までー🤗
Twitter: https://twitter.com/yugoes1021
Facebook: https://www.facebook.com/yugoes1021
LinkedIn: https://www.linkedin.com/in/yugoes1021/