Power Query 自定義列中的 M 代碼 | 電力BI

在今天的博客中，我將介紹的解決方案，包括用於將 M 代碼合併到 Power Query 自定義列中的簡單且通用的框架。您可以在本博客底部觀看本教程的完整視頻。

能夠使用 M 代碼在 Power Query 中創建列可以提高您解決更複雜問題的能力。

在我們進入解決方案之前，讓我們回顧一下問題的性質。我給了一個93年奧斯卡數據的Kaggle大數據集。我們想選出最佳女演員、獲獎者，尤其是那些獲得不止一個最佳女演員獎的女性，然後使用 Queryon Timeline 可視化她們獲得第二個獎項的那一年。

把這個問題放在一起時，我有很多目標。我們希望問題關注什麼，我們希望主要目標是什麼？

而在這一個里，一共有三個。

第一個是學習或開發 Group By 和 All Rows 構造的一些實踐。這是我一直非常關注的事情，因為它非常簡單地解決了範圍廣泛的問題，而且通常消除了對一些非常複雜的 DAX 的需求。

第二個目標特別針對那些不習慣在 M 中工作的人。我想創建一個問題，在編寫基本 M 代碼時會產生一些舒適感。我想強制採用這種思維方式來思考開發基本 M 代碼的感覺以及這樣做的過程。

根據 Gil Raviv 關於 Power Query 的書中的一章，Power Query 中大約 40% 的問題可以僅通過用戶界面來解決。但是，通過添加一些基本的 M 代碼和創建自定義列的能力，真的沒有那麼難。您可以將該百分比提高 80% 以上。您可以通過一些簡單的基本技能來加倍解決問題的能力。

我想做的第三件事是向那些不熟悉它的人介紹 Queryon Timeline 自定義視覺對象。我真的愛上了它。我已經在許多不同的實例中使用過它，我認為它只是提供了一種真正獨特的數據可視化方式。

目錄

• 第 9 週問題的解決方法
• 在 Power Query 中使用分組依據
• 合併 Table.AddIndexColumn M 代碼
• 使用 Queryon Timeline Custom Visual 進行可視化
• 結論

第 9 週問題的解決方法

在思考這個問題時，我想做一個思想實驗。如果不是 CSV 文件，我只是給你 93 張紙條（一張與每年的最佳女演員獎得主一起），解決方案會是什麼樣子？

您的解決方案可能看起來像我在下面得到的。

我想每個人都會有一個相對相似的解決方案。獲得概念性解決方案後，只需弄清楚如何讓 Power Query 執行其中的每個步驟即可。

因此，我將把它作為一個粗略的概述，然後介紹他們如何將這個概念性解決方案應用到 Power Query 中。我們將要使用的方法是我們的常駐 Power Query 大師 Melissa 所說的通過谷歌搜索學習。

現在，如果我們回到我們的概念模型，前兩個步驟包括按女演員對紙條進行分組，並丟棄任何只有一張紙條的組。所以，我們想做一個計數。如果我們不知道該怎麼做，我們可以進入谷歌並嘗試搜索一個術語。在這個例子中，我嘗試了“group in power query”。有了它，我們可以找到我們需要知道的關於如何進行分組的一切。

在 Power Query 中使用分組依據

然後我們可以進入 Power Query 並開始實施我們的解決方案。我所做的是，在這個奧斯卡獎的表格中，我剛剛瀏覽並提取了 CSV 文件，並篩選出每年的最佳女演員獲獎者。所以，我們只有 93 行的名稱和電影年份。

如果我們進入 Power Query 並轉到該表，我們要做的第一件事就是這個Group By。

我一直喜歡使用Advanced，因為它只是提供了一個稍微好一點的界面，即使我們正在做的事情非常簡單。

我們想按Name分組，然後讓我們有一列AllData，這就是我們的所有行。它將返回聚合，而不是只返回聚合，但我們想要的所有行仍然保持不變。

我們將有另一個名為Count的列，當我們要Count Rows時我們需要它。我們想找出每堆紙條的數量。然後，我們點擊確定。

然後我們可以單擊表格中的這個空白區域，我們只得到一張紙條。

但是對於那些有多個計數的，你會看到我們得到了所有的記錄。

接下來，我們要過濾掉 (1)。所以，我們基本上是在丟棄所有那些只有一個名字的一堆。選擇 1 並單擊確定。

我們現在只剩下 14 位多個獎項的獲得者。我們現在只剩下 14 位多個獎項的獲得者。如果我們接受並擴展它，我們可以取出Name，因為我們已經在這裡得到 Name 並且只保留year_film。

我們現在減少到 30 行，因為其中一些將有多個勝利。我們實際上什至可以去掉Count列，因為我們已經用一個去掉了 1。

現在我們進入概念模型的下一階段，即按日期升序對每組中的單據進行排序，然後選擇每組中出現的第二個日期。

排序很容易。我們可以返回到 Power Query 並按女演員升序排序，然後按日期升序排序。

它會告訴你一個和一個二。

所以我們已經很好地排序了，現在我們希望每個人都有一個索引或排名，然後能夠在每堆中取第二個。

合併 Table.AddIndexColumn M 代碼

所以現在我們回到我們的“通過谷歌搜索學習”方法，我們可以只搜索排名或索引，但我們也可以轉到Microsoft 參考指南。我創建了一個我一直使用的外部工具，它可以將您從 LuckyTemplates 跳轉到 Microsoft 參考指南。

在 Microsoft 參考指南中，所有內容都按功能分組，並且它有一個篩選框，您可以在其中搜索您需要的任何內容。在這種情況下，我們在篩選框中鍵入Rank或Index 。它沒有給我們任何排名，但對於索引，我們有這個Table.AddIndexColumn，它與我們正在尋找的內容相關並適用。它還提供了一些如何使用此功能的示例。

因此，讓我們進入 Power Query，讓我們按 Actress 重新組合我們的表。

這樣，我們按表獲得了 14 個獲勝者組。如果我們進入這些表中的任何一個，比如 Frances McDormand，我們可以看到該表是按日期排序的。

然後，這是重要的一步，即添加Power Query自定義列並輸入我們的 M 代碼。我們將擁有Table.AddIndexColumn，然後添加字段AllData。我們將在此處將我們的新列（作為文本）稱為Index，我們將從一 (1) 開始我們的 Index 並將其遞增一 (1)。

我們在這裡得到了這個完美的索引。

現在，我們可以在此處刪除我們的 AllData 列，因為所有內容都已在Custom中提取。接下來，我們可以將Custom 列展開，取出Name，因為我們這裡也已經有了Name。這樣，我們得到的是每個索引的編號索引。

最後，我們可以做我們在 Count 上所做的事情，即過濾掉所有在第二次勝利中的人。

我們開始了。這是我們的解決方案。

使用 Queryon Timeline Custom Visual 進行可視化

我們還想在這裡做一些其他事情，它們都是基於Queryon Visual的實現。我們將進行合併並僅添加圖片 URL。

所以我們將合併查詢，調出那個Actresses表，然後我們將在Name上合併。我們得到 14 到 14 行匹配，這很好。

然後在 Actresses 列中，我們將擴展到 URL。

現在我們有了包含 URL 的這一列。

Queryon Timeline 的知識還伴隨著另一件事。Queryon 在處理完整日期而不是年份時效果最好。因此，這是實驗帶來的東西，但我們可以使用Year列並創建Column From Examples。我們在這裡要做的只是把年份變成日期。

這將使格式更好。這是我不希望你馬上就知道的東西，但它是通過 Queryon 練習得到的。我們單擊“確定”，它作為日期值輸入。我們可以刪除索引表，因為我們不再需要它了，然後我們可以將列 year_film 的名稱更改為Year。

現在讓我們進入這個的可視化方面，我想強調 LuckyTemplates 成員之一 Jose 的條目，他在可視化方面確實做得很好。

回到 Power Query 進行可視化，值得一提的是，Microsoft 最近對他們認證自定義視覺對象的方式進行了一些更改。作為該認證的一部分，他們收緊了對外部 URL 圖像的使用。

如果您實際使用 App Source 上的 Queryon Timeline 版本（1.05 版），您將無法提取這些 URL 圖像。因此，您現在可以選擇使用這個 1.04，保留 URL 圖像，他們在他們的網站上有一種方法可以將二進制 64 位圖像直接嵌入到 PBIX 文件中。

現在我提到將那一年更改為日期字段，因此我們需要將其連接到我們的數據模型。

讓我們把它從一對一變成一對多，並在交叉過濾器上設置單向，然後讓這個關係生效。

接下來，對於圖像 URL 字段，我們需要確保在數據類別中，它不是顯示為未分類，而是顯示為圖像 URL。

現在我們已經準備好了一切，我們只需拖放Name、Date和URL字段。其餘部分在格式選項中變得非常簡單。

Power Query 表：如何動態合併列
如何在 LuckyTemplates 中合併查詢
從 Power Query 中的記錄和列表中提取值

結論

希望我讓您對將 M 代碼添加到您的曲目感到興奮。我已經向您展示瞭如何建立概念模型並實施“通過谷歌搜索學習”方法。我已經向您介紹了將 M 代碼合併到自定義列中並使用 Queryon Timeline 可視化結果的步驟。

我希望你覺得這很有用。觀看下面的完整視頻以獲取有關教程的更多詳細信息，並查看下面的鏈接以獲取更多相關內容。

一切順利！