Power Query 列変換チャレンジ: 積み重ねられたデータを順序付けされたテーブルに変換する

このブログでは、 Power Query の列からテーブル関数への変換を含む、Excel BI のスタックから順序付けされたデータへのチャレンジを検証します。タスクは、データの 1 つの列を、各タイプのデータが列を持つ適切なテーブルに変換し、その後、母集団のランクを追加することです。このチュートリアルのビデオ全体は、このブログの下部でご覧いただけます。

この Excel BI チャレンジは、 LuckyTemplates およびPower Query ツールの使用のスキルを練習して磨く絶好の機会です。また、その分野の専門家が使用している優れたテクニックもたくさんあり、学習して発見することができます。それでは、始めてみましょう!

目次

• リストの作成
• フィールド名の割り当て
• Power Query 列をテーブルに変換する
• Table.AddRankColumn
• Power Query 列を並べ替えられたテーブルに変換する
• 結論

リストの作成

作成する列には名前が必要なので、そのためのリストを作成します。まず、 詳細エディターウィンドウを開いて、上部にリスト列 ( lCols = ) という変数を追加します。その後、以下で強調表示されているように、この変数のすべての値を含むリストを入力し、Doneを押します。

データの各行には 5 つの値があります。 数式バーに List.Split と入力し、続いてページ サイズとして5 を入力して、これを分割します 。次にチェック マークを押すと、ネストされたリストを含むリストが返されます。

そして、空白部分の横をクリックすると、次のようにテーブルの下にプレビューが表示されます。

フィールド名の割り当て

したがって、テーブル内の各行について、それに関連付けられたすべての値を含むリストが作成されました。ただし、これをレコードに変換して、各値にフィールド名を割り当てられるようにしましょう。

数式バー内で、 先頭に List.Transform を追加し、最後に各Record.FromList を 追加します。アンダースコアを使用して、 Record.FromListのかっこ内のリストとフィールドを参照できます 。

そこで、リスト列変数Record.FromList( _, lCols ))を呼び出します。

したがって、リストを含むリストの代わりに、レコードを含むリストが作成されます。もう一度白いスペースの横に押し出して結果を見てみましょう。

上で、各項目にフィールド名が割り当てられていることがわかります。これを表に変換して続けます。

Power Query 列をテーブルに変換する

リストをテーブルに変換するには、数式バーの先頭に「Table.FromRecords」 と入力し 、開き括弧と閉じ括弧で数式を囲みます。チェックマークを押すと、以下の結果が得られます。

Table.AddRankColumn

母集団のランキングには、  Table.AddRankColumnと呼ばれる 比較的新しいM 関数を使用します。そこで、チェックマークの横にあるfx を押して 、この関数に入ります。

Rank by Population と呼ぶテキスト値を括弧内に入力して、関数の構築を続けます 。その後にカンマを入力し、比較基準を入力します。これは列 2022 Population です。

Power Query 列を並べ替えられたテーブルに変換する

降順で指定する必要があるため、  「Order.Descending」 と入力し、括弧で閉じます。関数が完了したので、もう一度チェック マークを押すと、以下の結果が得られます。

ご覧のとおり、ランキング列 ( Rank by Population ) が追加され、テーブルが降順に並べ替えられています。

新しい列名 ( Rank by Population )をコピーし、数式の先頭に Table.ReorderColumnsを追加します。テーブルと列の順序が必要なので、数式の最後にリストを作成し、コピーした新しい列名を貼り付けます。 

次に、リストの列 ( lCols )を追加し、括弧で閉じます。そして最後に、もう一度チェック マークを押します。

Excel BI のスタックから順序付けされた Power Query へのチャレンジを無事に完了しました。

LuckyTemplates 列の分割 Power Query チュートリアル: LuckyTemplates で複数行の Excel セルを分割する方法
共有ポイント リストを Excel または CSV ファイルにエクスポートする
Excel の複数のシートを LuckyTemplates に追加する

結論

Excel BI からの積み重ねられたデータから順序付けされたデータへのチャレンジを正常に完了しました。データの 1 つの列を適切なテーブルに変換し、各タイプのデータに列があり、その後、母集団のランクが追加されます。

まず、高度なエディターを使用してリストのセットを作成しました。列をドリルダウンし、リストを分割し、フィールド名を割り当てました。次に、データを表形式に変換し、関数を使用して列を記録しました。

このブログで学んだことはすべて、将来の LuckyTemplates および Power Query 変換列の取り組みに必ず役立ちます。