LuckyTemplates で複数の列を動的にアンピボットする

このチュートリアルでは、List.Zip を使用して Power Query の複数の列をアンピボットする方法について以前に説明したトピックを再取り上げます。LuckyTemplates のメンバーが、そのメソッドを動的にする方法についてこのチュートリアルの完全なビデオは、このブログの下部でご覧いただけます。

これを上級とラベル付けしたのは、これから多数の M 関数を作成することになるためであり、そのうちのいくつかはあなたにとって初めてのものである可能性もあります。

そうは言っても、Power Query に移りましょう。

目次

• 表形式のデータ
• コードを変更して複数の列を動的にアンピボットする
• 複数の列を動的にアンピボット: カスタム列ロジックの追加
• 結論

表形式のデータ

ご覧のとおり、このデータは分析に適した適切なタブレット形式に変換されています。このプロセスは非常に重要ですが、ここでは説明しませんが、各ステップについて簡単に説明します。

ソース ステップでは、上記の各属性の時間と連絡先である値のペアが継続的に繰り返されています。

[適用されたステップ]で、残りのステップを確認できます。上部の行をいくつか削除し、空の列を削除し、テキスト値をクリーンアップして埋め込みました。次に、データを転置し、空のレコードを削除し、列 1 と列 2 をマージし、テキスト値を再度クリーンアップして、データを転置して戻しました。

後で詳しく説明するカスタム ステップを作成し、リストから値を抽出し、最後にタイプを変更しました。

コード全体を見てみましょう。ここで注目するのはList.Zip関数です。簡単にまとめると、単一のリストをパラメータとして受け取り、リストを返します。結果として、このインスタンスのこの 1 つのリストには 3 つの別個のリストが含まれていることがわかります。

最初のリストを調べると、ハードコーディングされた属性名が含まれており、入力されたテキスト値だけが含まれています。 2 番目のリストには、時間値のハードコーディングされた列参照が含まれています。最後のリストには、連絡先値のハードコードされた列参照が含まれています。

List.Zip は、これら 3 つのリストのそれぞれから要素を取得し、それらを新しいリスト内の位置に基づいて結合します。たとえば、これらのリストの最初の各要素が新しいリストに結合されます。同じことが、そのリストの 2 番目にリストされているすべての値にも起こります。

属性が時間の経過とともに変化する可能性が低い場合は、ハードコードされた値が問題になる可能性があります。したがって、次回、すべてのアイテムが存在しなかったり、新しいアイテムが存在したりすると、問題が発生する可能性があります。

コードを変更して複数の列を動的にアンピボットする

では、これをダイナミックにするにはどうすればよいでしょうか?

最初にコードをステップ実行して、それを達成するための別のステップを設計することなく、これらの属性値を取得できる場所があるかどうかを確認します。したがって、コードをステップバックして説明します。このRemoveNullステップは完璧です。

すべての属性値が列 1 にリストされていることがわかります。ユーザー インターフェイスを使用してそれらを抽出できます。

そのヘッダーを右クリックし、その下で[新しいクエリとして追加]を選択します。

このリストを取得しました。これは良いニュースです。前に見たハードコードされたリストを動的なリストに置き換えたいからです。私たちがしなければならないのは、値を整理することだけです。

これらの null を削除することから始めましょう。そこで、数式バー内にList.RemoveNullsを追加します。ご覧のとおり、すべての項目が繰り返されていますが、これは値のペアによるものです。時間ごとに 1 回リストされます。

そして、連絡先についても、それらの重複を削除するために、List.Distinctを追加できます。このロジックを作成したので、あとはそれをコピーしてサンプル クエリに貼り付けるだけです。

サンプル クエリで、詳細エディタを開いて新しい変数名を作成します。それをAttributeListと呼びましょう。次に、コードを貼り付けてカンマを入力します。

次に、カスタム ステップに戻りましょう。List.Zip 内で、最初のリストを変数名 (AttributeList) に置き換えることができます。

次に、2 番目のリストを詳しく見てみましょう。これが列名を参照していることがわかっており、それらの列名はそれぞれ、そのリストに既に存在するものと同じ属性で始まり、その後にスペースとテキスト「時間」が続きます。

各項目に接尾辞を追加するだけです。それでは、1 つ前のステップに戻り、カスタム列を追加してロジックを作成しましょう。

複数の列を動的にアンピボット: カスタム列ロジックの追加

これは単なる一時的な列なので、適切な名前を付ける必要はありません。完了したら削除します。

リスト内の値を変更するには、List.Transform関数を使用できます。AttributeListの値を変換する必要があるので、それを貼り付けます。

ここのヘッダーでは、テキスト値が大文字で始まり、残りが小文字であることがわかります。属性には大文字の単語のみが含まれているため、そのテキスト値も変換する必要があります。そして、アンダースコアを使用してリストの各項目にアクセスし、そのサフィックスを追加します。

これで、列にリスト値が得られます。どれを選択しても問題ありませんが、ここでいずれかの空白部分の横をクリックしてください。

ページの下に、作成したリストのプレビューが表示されます。これらの列ヘッダーの大文字と小文字が適切に区別され、このリストで表示される列ヘッダーと一致することがわかります。

これで、アクセスしたいフィールドをレコードから特定する手段ができました。そこで、カスタム列ダイアログ ボックスを再度開き、Record.SelectFieldsを追加しましょう。レコードをレコードとして必要とするので、そのアンダースコアを使用して現在のレコードにアクセスできます。そして、これらすべてのフィールド名のリストを指定したので、右括弧を追加してOKを押します。

したがって、リストはもうありませんが、レコードは取得できるようになりました。もう一度空白の横をクリックして、そのレコードの内容を表示してみましょう。ここでは、レコード フィールド名とレコード フィールド値がリストされていることがわかります。

ただし、関心があるのはレコード フィールドの値だけなので、それを抽出する必要があります。カスタム列ダイアログ ボックスに戻り、Record.FieldsValuesを追加しましょう。

再びリストを取得しましたが、記録はなくなりました。もう一度空白の横をクリックすると、それらの値だけを含むリストが表示されることがわかります。見た目は良いですね。

最後の右かっこを除いて、作成したロジック全体をコピーしましょう。一時列は不要になったので、削除できます。

数式バーで 2 番目のリストを選択し、ロジックに置き換えます。

連絡先のレコード フィールド値を含むリストを作成するには、サフィックスを変更するだけで済みます。したがって、最後のリストも選択して、ロジックに貼り付けることができます。

次に、これをhours の代わりにcontacts に変更しましょう。

エラーはなく、見た目も良好です。これは、複数の列をアンピボットするための動的なソリューションになりました。

LuckyTemplates 列のピボット解除 – Power Query チュートリアル
LuckyTemplates で列のピボットを解除する方法 LuckyTemplates の
ピボット解除とピボットの基本 – クエリ エディターのレビュー

結論

これが、 List.Zip を使用してで複数の列を動的にアンピボットする方法です。

新しい属性が入ってくると、それは AttributeList によって自動的に選択されます。そして、項目が存在しなくなると、そのリストには表示されなくなります。

楽しんでいただけたでしょうか。このチュートリアルの詳細については、以下の完全なビデオをご覧ください。LuckyTemplates での複数の列のピボット解除に関する詳細コンテンツについては、以下のリンクをご覧ください。

ではごきげんよう

メリッサ