LuckyTemplates カスタム クエリ関数

今日のチュートリアルでは、扱いが難しい一般的な形式のスタック データを処理する LuckyTemplates カスタム クエリ関数を構築します。この手法は、複数のレポートや複数のテーブルで簡単に再利用できます。このチュートリアルの完全なビデオは、このブログの下部でご覧いただけます。

これは、私が最近行ったチュートリアルの続きで、スタックされたデータの一般的な扱いが難しい形式を扱い、Modulo、Pivo​​t、および Power Query のいくつかのクリーニング関数を使用して、そのデータを整理された機能的な形式に変換しました。

このプロセスを繰り返す必要がある可能性が非常に高くなります。このタイプの形式がコピー アンド ペーストによって作成された場合、おそらく再び同じことが起こるでしょう。したがって、この場合は、名前の付け方やテーブルの内容に関係なく、複数のレポートや複数のテーブルで再利用できるカスタム関数を構築する必要があるでしょう。

目次

• LuckyTemplates カスタム クエリ関数を作成およびデバッグする方法
• 結論

LuckyTemplates カスタム クエリ関数を作成およびデバッグする方法

これについては、Power Query の説明に移りましょう。アドバンス エディターのデータ テーブルに移動すると、スタック データから目的の形式に最終的にどのように変換したかを示す M コードが表示されます。

これを再利用したい場合は、「データ」を右クリックして「関数の作成」と言うだけです。

パラメーターを参照しないと書かれていますが、パラメーターなしで関数を作成しても問題ありません。アドバンス エディターでパラメータを追加するので、「はい」と答えることにします。生の形式で 3 つの行があるため、この関数をUnstack – 3 Elementsと呼びます。2 行のフォーマットがある場合は、おそらく 2 要素または 4 要素の別のフォーマットを作成するでしょう。

これで、この Invoke が完成しました。パラメーターはありません。詳細エディターに移動して、この作業を開始しましょう。

まず、このソースは必要ありません。関数パラメータを使用してソースを定義します。

入力されるパラメータを定義したいと思います。これを開き括弧で囲み、次にパラメータ名を使用します。これをStackと呼びます。これは、電子メール アドレスのペーストから得られた書式設定されていないスタック データである 1 列のデータになります。

これをリストとして取得し、その結果をテーブルにする必要があります。これは、単一の列から複数の列にアンピボットすると、リストからテーブルに移行するためです。そして今度は、インデックスを追加し、Modulo アンピボットを開始し、再フォーマットするために必要なすべての作業を実行できるように、受信したリストをテーブルに変換する必要があります。

これをConvertStackと呼びます。これはTable.FromListという関数を使用します。多くの M 関数と同様に、それはまさにその言葉通りのことを行います。必要なのはリストだけです。この場合、リストはスタック リストになります。次に、ここの Source を ConvertStack に置き換える必要があります。かなりいい感じですね！構文エラーはないので、「完了」をクリックしましょう。

これで、私たちが望んでいたもの、つまり列を選択できる機能が提供されるようになりました。

次に、形式が間違っているデータである TestData を選択します。[値] 列を選択し、[OK] をクリックします。その後、「呼び出し」をクリックします。

ただし、このエラーが発生します。このエラーが何であるかを特定するのは非常に困難です。カスタム関数の問題の 1 つは、カスタム関数全体で 1 つのステップを取得するだけの適用ステップが分割されないことです。しかし、これから説明するデバッグでそれを回避する方法があります。ここで何が起こっているのかを理解するのがずっと簡単になります。

現時点ではこれを関数として実行する代わりに、クエリとして実行します。Stack 呼び出しを手動で開始するだけです。Invoke でスタックを選択するという対話を通じてスタックを描画するわけではないため、スタックが何であるかを定義する必要があります。したがって、Stack は TestData と等しく、Value 列は TestData になります。

それが行うことは、それを応用ステップに分割することです。これは、なぜこれが機能しないのかを理解するのに役立ちます。Stack をプルすると、興味深い点が 1 つあることに注目してください。最初は TestData[Value] をプルしますが、これをテーブルに変換すると、Value が列ヘッダーになるのではなく、列ヘッダーが Column1 になります。

エラー メッセージを思い出してください。それは、[値] 列が見つからないというものでした。Value 列が見つからない理由は、Table.FromList 関数によってその名前が Column1 に変更されるためです。

適用されたステップを下に進むと、追加されたカスタム ステップでエラーが発生していることがわかります。ここでレコードの値が見つかりませんでした。

したがって、詳細エディターに移動すると、その Custom フィールドが見つかり、それが Text.Remove 内にあることがわかります。ここでは、不要なジャンク文字を削除していますが、まだ参照しています。値フィールド。これをColumn1に変更します。同様に、「削除された列」フィールドでは「値」を参照しているため、これも「列 1」に変更します。

そして、適用されたステップの最後に到達すると、完璧な最終結果が得られます。

これが正しく機能していることがわかったので、あとはやるべきことが 1 つだけあります。デバッグするために、カスタム関数をクエリに変換したことを思い出してください。そこで、今度はそのクエリを取得して関数に戻す必要があります。

Power Query でのカスタム テキスト クリーニング関数の作成
Power Query の M コード カスタム列 | クエリ エディターのLuckyTemplates関数のクエリと演算子

結論

このチュートリアルでは、このカスタム関数を構築し、デバッグし、テストしましたが、うまく機能しています。そのため、3 要素のスタックがあればいつでも、そのテーブルを選択し、テーブル内のその列を選択して、そのカスタム関数を実行できるようになりました。次に、Modulo を適用し、アンピボットして、クリーンアップします。

これにより、カスタム関数の作成に関して優れたツールが提供されるはずです。また、デバッグに関するトリックとして、関数からのクエリに戻し、デバッグ後に元に戻すというトリックも覚えておいてください。これは、カスタム関数のデバッグをはるかに簡単にする非常に貴重なツールです。

ではごきげんよう！