クエリの列名をダイナミックに変更する~M関数使用~

 今回は、クエリのデータの中味ではなく、列名を別クエリ(別表)からダイナミックに変更する方法を解説したいと思います

 上のGIF画像では、画面左にある表の内容を変えたら、右にある列名が変更されています

 この仕組みは、クエリ上で列名を変更した後、エディタ内に記録された内容にM関数を間接的に組み込む形で作成します

 一から、仕組みを構築するわけではないので、最初の印象よりも簡単に作成できます

今回のポイント

入れ子のリスト

仮に、空のクエリに下の数式を入力してみたとしましょう

= {{1,2},{3,4},{5,6}}

すると、次の画像のようにリストが複数できます

*****Sponsered Link***** ************************

今の段階では、この内容が後で出てくるということだけ覚えておいてください

使用するM関数

今回は2つのM関数を使用します

Table.RenameColumns ➡列名変更

書き方:Table.RenameColumns(テーブル名, リスト名)

Table.ToColumns ➡テーブルからリスト(入れ子)作成

書き方:Table.ToColumns(テーブル名)

今回のポイントを確認したところで、本格的な解説を始めたいと思います

クエリの列名変更(仮の処理)

まずは、元のデータ/テーブル名:AllDataをテーブル化してエディタで開きます

そして、エディタ内で全ての列名を変更します

すると、数式バーは次のような表示になっています

= Table.RenameColumns(ソース,

{{“年度”, “Year”}, {“製品別”, “Product”}, {“地域”, “Region”}, {“売上金額”, “Sales”}}

)

 今回のポイントで紹介したM関数「Table.RenameColumns」が入力されています

そして、第二引数は入れ子になったリストになっています

こちらの第二引数については、後で修正します

列名変換のための入れ子リスト作成

今度は、新旧の列名を変換するためのデータをテーブル化してエディタで開きます

エディタで開いたら、変換タブで行列を入れ替えます

*行列の入れ替え前にヘッダー行を下げておいてください

ここから入れ子のリストを、今回のポイントで紹介したM関数「Table.ToColumns」を使用して作成します

こちらは、下の画像の「詳細エディター」で作業を行います

下の図の2行目/転置されたテーブルの下にM関数を追加します

注意点は2点あります

1点は、2行目が最終行でなくなるので、「,」を上の画像の黄色い印のように加えること

2点目は、inの後はletの最終行のリストに置き換えること

この2点です

詳細エディターでの作業を終えたら、エディタは次の画像のようになっています

リストが入れ子になっているのがよく分かります

リスト名は上の画像のように「列名」としておきます

入れ子リストの組み入れ

前述の仮で列名を変えてあるクエリに、新たに作成したリスト「列名」を組み入れます

AllData

上の画像の数式バーの部分を抜き出すと、次のようになっています

= Table.RenameColumns(ソース,

{{“年度”, “Year”}, {“製品別”, “Product”}, {“地域”, “Region”}, {“売上金額”, “Sales”}}

)

こちらの第二引数の入れ子リストを、前述のリスト「列名」に入れ替えます

これで、列名のリストの基となるデータを変えれば、ダイナミックに「AllData」クエリの列名が変更されていきます

<まとめ>

 今回は、入れ子のリストの仕組み、並びにM関数を2つ使用して、クエリの列名をダイナミックに変更する仕組みを解説しました

今回の解説の入り口は、空のクエリで入れ子のリストを作成する点です

こちらはよく復習されておくと、色んな場面で応用が効くと思います

後、記事の途中で詳細エディターを使用した場面がありましたが、少し補足をしておきます

通常、詳細エディターを開くと次のようになっています

 上の図のように、ソース➡ソース、追加されたカスタム➡追加されたカスタムという風にテーブル名が次の行に引き継がれていきます

今回もテーブル名をM関数/Table.ToColumnsの()の中に引継ぎました

画像に alt 属性が指定されていません。ファイル名: image-57.png

なお、「,」はletの最終行には付けないので、この点はご注意ください

最後まで記事の最後までお読みくださり、誠にありがとうございました

参考までに今回使用したファイルも添付します

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

*****Sponsered Link***** ************************

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です