Power Query カスタム列の M コード | パワーBI

今日のブログでは、 M コードを Power Query カスタム列に組み込むためのシンプルで一般化可能なフレームワークを含む、 LuckyTemplatesに対する解決策を紹介します。このチュートリアルの完全なビデオは、このブログの下部でご覧いただけます。

M コードを使用して Power Query で列を作成できると、より複雑な問題を解決する能力が高まります。

解決策に入る前に、問題の性質を確認しましょう。Kaggle からの 93 年間の Oscar データのビッグ データ セットを提供しました。私たちは、最優秀女優賞、受賞者、特に複数の最優秀女優賞を受賞した女性を抽出し、クエリオン タイムラインを使用して、彼女たちが 2 回目の賞を受賞した年を視覚化したいと考えました。

この問題をまとめるとき、私にはいくつかの目的がありました。問題の何に焦点を当てたいのか、また主な目的を何にしたいのか?

そして、この作品では、それらが3つありました。

1 つ目は、Group By および All Rows 構造を学習または実践することでした。これは、さまざまな問題を非常に簡単に解決し、多くの場合、非常に複雑な DAX の必要性を排除するため、私が特に注目しているものです。

2 番目の目的は、特に M での作業に慣れていない人向けです。基本的な M コードをある程度快適に書くことができる問題を作成したかったのです。基本的な M コードの開発とそのプロセスがどのようなものであるかについて、そのような考え方を強制したかったのです。

Gil Raviv の本の Power Query に関する章によると、Power Query の問題の約 40% はユーザー インターフェイスだけで解決できるそうです。しかし、いくつかの基本的な M コードを追加し、カスタム列を作成する機能があれば、実際にはそれほど難しいことではありません。その割合を 80% 以上増やすことができます。いくつかの基本的で簡単なスキルを身につけることで、問題解決能力を倍増させることができます。

3 番目にやりたいのは、Queryon Timeline カスタム ビジュアルをよく知らない人に紹介することです。本当に大好きになった作品です。私はこれをさまざまなインスタンスで使用してきましたが、データを視覚化する非常にユニークな方法を提供すると思います。

目次

• 今週の問題 #9 に対する解決策のアプローチ
• Power Query での Group By の使用
• Table.AddIndexColumn M コードの組み込み
• Queryon タイムライン カスタム ビジュアルを使用した視覚化
• 結論

今週の問題 #9 に対する解決策のアプローチ

この問題を考えるにあたり、思考実験をしてみたいと思いました。CSV ファイルの代わりに、93 枚の紙片 (毎年の主演女優賞受賞者が 1 枚ずつ記載されているもの) を渡したとしたら、解決策はどうなるでしょうか?

あなたの解決策はおそらく以下のようなものになるでしょう。

それに対しては、誰もが比較的似たような解決策を持っていると思います。概念的な解決策を理解したら、後は Power Query でこれらの各ステップを実行する方法を理解するだけです。

したがって、これを大まかな概要として取り上げてから、この概念的なソリューションを Power Query でどのように実用化するかを説明します。私たちが使用しようとしているアプローチは、常駐の Power Query の第一人者である Melissa が「Google で学ぶ」と呼んでいるものです。

ここで概念モデルに戻ると、最初の 2 つのステップには、伝票を女優別にグループに分類し、伝票が 1 枚だけあるグループを破棄することが含まれます。そこで、カウントを行っていきたいと思います。その方法がわからない場合は、Google にアクセスして用語を検索してみてください。この例では、「パワークエリでグループ化」を試してみました。これで、このグループ化の方法について知る必要があるすべてを見つけることができます。

Power Query での Group By の使用

次に、Power Query に飛び込み、ソリューションの運用を開始します。私がやったことは、このオスカーの表で CSV ファイルを調べて取り込み、各年の主演女優賞受賞者に絞り込んだことです。つまり、映画の名前と年だけの 93 行が得られます。

Power Query にアクセスしてそのテーブルに移動すると、最初に実行したいのはこのGroup Byです。

私は、たとえやっていることが非常に単純であっても、アドバンストのほうがわずかに優れたインターフェイスを提供するという理由だけで、アドバンストを選ぶのがいつも好きでした。

Nameでグループ化したいので、 AllDataという列を用意します。これがすべての行になります。集計だけを返すのではなく、集計を返しますが、必要なすべての行はそのまま残ります。

Countという名前の別の列があり、 Rows をカウントするときに必要になります。各山のスリップの数を調べたいと思います。次に、「OK」をクリックします。

次に、テーブル内のこの空白部分をクリックすると、伝票が 1 枚だけ取得されます。

ただし、複数のカウントがあるものについては、すべてのレコードが取得されることがわかります。

次に、(1) をフィルタリングします。したがって、私たちは基本的に、名前が 1 つしかない山をすべて廃棄することになります。1 を選択し、「OK」をクリックします。

そして、複数の賞を受賞した 14 名が発表されました。そして、複数の賞を受賞した 14 名が発表されました。これを展開すると、ここにはすでに Name があり、year_filmだけを保持しているため、 Name を取り出すことができます。

これらの行の中には複数回勝利するものもあるため、現在は 30 行まで減りました。そして、すでに 1 を 1 つだけ捨てているので、実際にはCount列を削除することもできます。

ここで、概念モデルの次の段階に進みます。この段階では、各グループの伝票を日付の昇順で並べ替え、各グループ内で 2 番目に発生する日を選択します。

並べ替えは簡単です。Power Query に戻って、女優ごとに昇順で並べ替え、次に日付ごとに並べ替えを行うことができます。

そして、1 と 2 が表示されます。

うまく並べ替えができたので、各項目にインデックスやランキングを作成し、各山の 2 番目の項目を取得できるようにしたいと考えています。

Table.AddIndexColumn M コードの組み込み

そこで、ランクやインデックスを検索するだけでなく、「Microsoft Reference Guide」を参照することもできる「Google で学習する」アプローチに戻りました。LuckyTemplates から Microsoft リファレンス ガイドに移動するだけの、いつも使用する外部ツールを作成しました。

Microsoft リファレンス ガイドでは、すべてが機能ごとにグループ化されており、必要なものを検索できるフィルター ボックスがあります。この場合、フィルター ボックスに「ランク」または「インデックス」と入力します。Rank については何も得られませんが、Index については、探しているものに関連し、適用可能なTable.AddIndexColumnがあります。この関数の使用方法の例もいくつか示します。

それでは、Power Query に進み、女優ごとにテーブルを再グループ化しましょう。

これで、テーブルごとに 14 人の勝者グループが得られます。これらのテーブルのいずれか (たとえば、Frances McDormand) にアクセスすると、テーブルが日付でソートされていることがわかります。

そして、これが大きなステップです。Power Queryカスタム列を追加し、M コードを入力します。Table.AddIndexColumnを用意し、次にフィールドAllDataを追加します。ここでは新しい列 (テキストとして) をIndexと呼び、インデックスを 1 から開始して 1 ずつ増やします。

そして、ここで完璧なインデックスを入手できます。

すべてがCustomで取得されているため、ここで AllData 列を削除できます。次に、Custom 列を展開して、Name を取り出します。これは、ここにも既に Name があるためです。これで、それぞれの番号付きインデックスが得られました。

最後に、Count で行ったことと同じことを行うことができます。これは、2 番目の勝利に含まれるすべてのものをフィルターで除外することです。

そして、行きます。それが私たちの解決策です。

Queryon タイムライン カスタム ビジュアルを使用した視覚化

ここで実行したいことが他にもいくつかありますが、それらはすべて Queryon Visualの実装に基づいています。マージを実行し、画像の URL を追加するだけです。

そこで、クエリをマージし、 Accessesテーブルを取得し、Nameでマージします。14 対 14 行の一致が得られ、これは良好です。

次に、Accesses 列で展開し、URL まで展開します。

これで、この列に URL が表示されました。

Queryon タイムラインの知識に関連するものがもう 1 つあります。Queryon は、年ではなく完全な日付を扱う場合に最適に機能します。したがって、これは単なる実験に伴うものですが、Year列を取得して、Column From Examplesを作成できます。ここでやりたいことは、年を日付に変換することです。

そうすることでフォーマットが改善されます。それは、あなたがすぐに分かるとは思っていませんが、Queryon を使って練習すれば分かるようになります。「OK」をクリックすると、それが日付値として入力されます。インデックス テーブルはもう必要ないので削除し、列 year_film の名前をYearに変更します。

ここで、この視覚化の側面に移りましょう。視覚化に関して本当に素晴らしい仕事をした、LuckyTemplates メンバーの 1 人、Jose のエントリを強調したいと思います。

視覚化のための Power Query に戻りますが、Microsoft が最近、カスタム ビジュアルの認証方法にいくつかの変更を実装したことに言及する価値があります。その認定の一環として、外部 URL 画像の使用を厳格化しました。

実際にアプリ ソース (バージョン 1.05) にある Queryon Timeline のバージョンを使用している場合、これらの URL イメージを取り込むことはできません。そのため、URL 画像を保持したままこ​​の 1.04 を使用するオプションが提供されます。サイトにはバイナリ 64 画像を PBIX ファイルに直接埋め込む方法があります。

ここで、その年を日付フィールドに変更すると述べたので、それをデータ モデルに接続する必要があります。

これを 1 対 1 から 1 対多、クロス フィルターの単一方向に変更して、この関係をアクティブにしましょう。

次に、画像 URL フィールドについては、[データ] カテゴリで [未分類] ではなく [画像 URL]として表示されていることを確認する必要があります。

すべての準備が整ったので、Name、Date、およびURLフィールドをドラッグ アンド ドロップするだけです。残りの部分は、形式オプションで非常に簡単にクリーンアップされます。

Power Query テーブル: 列を動的に結合する方法
LuckyTemplates でクエリを結合する方法
Power Query でレコードとリストから値を抽出する

結論

M コードをレパートリーに追加することに興奮していただければ幸いです。概念モデルを設定し、Google で学習するアプローチを実装する方法を説明しました。M コードをカスタム列に組み込み、Queryon Timeline を使用して結果を視覚化する手順を説明しました。

お役に立てば幸いです。チュートリアルの詳細については、以下の完全なビデオをご覧ください。その他の関連コンテンツについては、以下のリンクをご覧ください。

ではごきげんよう！