文本文件中的維度表：Power Query 解決方案

正在進行的本週問題中的問題 #2 ，我們在該論壇中討論瞭如何將雜亂的數據分類為乾淨的維度表。這是我們在 LuckyTemplates 舉辦的這項新計劃的第二次迭代。我對這個系列特別感興趣，因為它為每個人提供了定期進行更多練習的機會。您可以在本博客底部觀看本教程的完整視頻。

在每個月的第一個星期三，有一個DAX 挑戰，在第三個星期三，有一個power query 挑戰。

這是探索、發現和學習有關您需要在 LuckyTemplates 中利用的這些語言的新事物的絕佳機會。

您會在 LuckyTemplates 論壇上找到有關“本週問題”的類別。

主要關注的是過程，而不是結果。

如果你成功地將一個問題分解成更小的部分，你將能夠解決你面臨的任何問題。

梅麗莎·德·科特

Brian 之前談到了一種稱為橡皮鴨調試的技術。如果你錯過了，一定要看看他的視頻。當您遇到這些挑戰時，它可以為您提供幫助。

首先，讓我們檢查手頭的任務。我們有一個雜亂的文本文件，需要將其轉換為合適的維度表。

現在，當我查看這樣的數據時，在大多數情況下，它都與文本清理、刪除不需要的字符、修剪、大寫單詞等有關。

但是我們還需要從這個堆棧數據中檢索所有行，並將它變成每個國家的一行。我將其稱為堆棧數據，因為所有字段名稱都在每個國家/地區的單個列中重複。

我有一些 Excel 經驗，在我看來它最強大的功能之一是數據透視表。

目錄

• Excel 中的數據透視表
• 使用Power Query創建維度表
• 自定義文字清理功能
• 旋轉數據
• 結論

Excel 中的數據透視表

數據透視表讓我可以逐段查看數據。根據您放置在行部分的內容，數據透視表將該段的每次出現都壓縮到一行中。

然後，您可以通過將字段拖到列部分來進一步拆分它。

我已經創建了一個例子來說明它的樣子。在這裡，我們擁有與文本文件中幾乎相同的數據。

在下一頁上，我創建了一個空的數據透視表。

現在，我沒有向您展示的是我在這裡有三列，而不是我已經向您展示的兩列。

我要把我的片段拖到行中。

由於第 1 列有字段名稱，我將把它拖到列部分。

第 2 列包含所有字段值，因此我將把它拖到值部分。

我們可以在這裡看到數據透視表無法處理文本字符串。它計算了它們，但它確實表明我們對每個字段都有一個值。

現在讓我們來看看我創建的那個片段。因此，我將返回數據並取消隱藏我的列。您可以看到它只是一個索引號，用於標識仍然相互堆疊的每個獨立數據塊。

所以對我來說，關鍵的轉換將是數據的一個樞軸，將其恢復為表格格式。

在論壇中，我看到了處理此問題的其他方法。除了使用樞軸動作之外，還有其他方法可以獲得表格格式，它們同樣有效。如果您對此感興趣，請訪問論壇並開始探索。

使用Power Query創建維度表

讓我們檢查電源查詢並查看我的解決方案。

就個人而言，我認為 UI 在為我們編寫大部分 M 代碼方面做得很好。所以我傾向於盡可能多地使用用戶界面來設計我的查詢。

一旦查詢完成了我需要它做的事情，我將進入高級編輯器並檢查 M 代碼以查看是否可以修改它。讓我們看看它是什麼樣的。

這是我使用用戶界面構建的基本組。

您可以看到右側的應用步驟中有很多步驟。

現在這本身不是問題，但只要查看這些步驟，您就會發現有很多轉換可以組合在一起。

讓我們打開高級編輯器。

我們看到這個查詢有 31 個步驟。

我還向包含 31 個步驟的同一個查詢添加了一些註釋，但我將其分成了幾個部分。

我做的第一件事是刪除更改類型步驟。我建議創建一個自定義函數來執行所有這些文本轉換步驟。

這裡有很多評論，但我只想在這篇博文中強調兩件事。首先是用於文本清理目的的自定義函數。

第二個是將此類數據轉換回正確的表格格式的關鍵步驟。

自定義文字清理功能

讓我們回到構建我的查詢的早期階段，在那裡我有所有這些分組步驟來清理這些文本：第 1 列和合併的列。

我還添加了一個額外的自定義列。它的唯一目的是構建我的自定義文本清理功能。我在合併列上調用了它。

這樣，我不必一次性編寫函數，而是逐步構建它，一次一步，在檢查上一步的結果後添加一個新的轉換。

我們來看一下文字清理功能的M代碼。

如您所見，我有多個步驟。當我構建這個文本函數時，我在查詢之間來回切換以查看結果以查看要構建的內容以及接下來要更正的內容。

有了這個結果，我執行了我需要的所有轉換。我在這裡使用的一些 M 函數是在我構建初始查詢時由用戶界面提供的，例如文本。修剪功能。但是，使用的其他功能不是。

如果你不熟悉它們，你可以在線查找M 公式指南中的所有M 功能。這是您需要去的鏈接。

您會找到一個專門介紹電源查詢和功能的部分。

如果向下滾動，您會找到有關文本功能的部分，每個部分都以概述開頭。在 power query 和 formula M 語言中有一個所有文本函數的列表。

如果您正在尋找特定的轉換，可以在此處查找。

旋轉數據

創建此維度表時我想強調的第二部分是數據本身的旋轉。讓我們也仔細看看這個。

我首先添加了一個索引。我更新了該索引以正確分割數據塊。我通過返回第 1 列中包含文本國家/地區的每一行的索引號來完成此操作，然後填寫該值。

我們需要做的就是旋轉數據本身。在轉換選項卡上，您會找到數據透視列。選擇 Column 1 後，單擊 Pivot Column。

它將使用第 1 列的值作為新的列名。它還想知道這些字段名稱的值在哪裡。這些在我們的合併列中。

如果 Excel 可以處理文本值，Power Query 也可以，因為它有高級選項設置。我們需要做的就是選擇Don't Aggregate讓它能夠處理文本值。

單擊“確定”後，我們可以看到我們的數據已被透視。

結論

上圖是最終的查詢結果。我希望您喜歡我們如何重塑提供的文本文件中的雜亂數據，並將其轉變為適合分析的干淨維度表。

如果您喜歡這篇博文，請訂閱 LuckyTemplates 頻道，這樣您就不會錯過任何新內容。

我希望在未來的“本週難題”挑戰中見到大家。

梅麗莎