Power Query 語言流最佳實踐

在本教程中，您將了解 Power Query 語言流及其一些最佳實踐。您還將了解如何輕鬆轉換數據以獲得最佳結果。數據轉換將優化您的報告並使其看起來引人注目。

目錄

• 設置沒有 Power Query 語言流的數據類型
  • 使用轉換選項卡設置數據類型
  • 使用列標題設置數據類型
  • 使用列圖標設置數據類型
• 使用 Power Query 語言流設置數據類型
  • 刪除不需要的列
  • 為列分配適當的數據類型並限制行
  • 整合 Power Query 語言流程中的冗餘步驟
• 組織查詢以獲得更好的 Power Query 語言流
• Power Query 語言流程總結
• 結論

設置沒有 Power Query 語言流的數據類型

您應該始終執行的最低限度的轉換是重命名和設置列的數據類型。

數據類型用於對M中的值進行分類。列名稱旁邊的圖標顯示為字段分配的數據類型。在此示例表中，您可以在列名旁邊看到一個 ABC123 圖標。該圖標代表 Any 數據類型，表示尚未將數據類型分配給該列。 

在書籍之外，啟用了自動類型檢測。這會在您的查詢中自動生成更改類型步驟。即使已啟用，請始終確保Power Query正確猜測所有表中每一列的數據類型。

要啟用或禁用該設置，請按照以下步驟操作。

在全局選項下，您可以選擇可用的三個類型檢測選項之一。

在當前文件選項下，您可以根據全局設置切換類型檢測選項。

使用轉換選項卡設置數據類型

用戶界面提供了多種方式來設置列的數據類型。在“轉換”選項卡的“任何列”部分，您會找到“檢測數據類型”按鈕。

如果您選擇所有列並單擊該按鈕，它將自動設置所有列的數據類型。但是，由於它只掃描前 200 行，您仍然需要驗證 Power Query 是否具有正確的數據類型。

如果您從表中選擇一列，您可以在任何列部分中看到它的數據類型。

如果單擊下拉按鈕並選擇文本，所選列上的圖標將從ABC123更改為僅ABC。

然後，您將在 Applied Steps 窗格中看到 Changed Type 步驟。

在主頁選項卡中，您還會找到設置數據類型的選項。

使用列標題設置數據類型

另一種方法是右鍵單擊標題並選擇Change Type。從那裡，您可以為您的列選擇合適的數據類型。

使用列圖標設置數據類型

設置數據類型最常用的方法是單擊列名稱旁邊的圖標。它將向您顯示所有可用的數據類型。

在示例表中，將OrderDate列的數據類型設置為Date。

根據您選擇的列的數據類型，Power Query 在“轉換”選項卡、“添加列”選項卡和“智能篩選器”部分中提供一組適用於該特定數據類型的轉換和選項。

因此，如果您選擇一個日期列並轉到“轉換”選項卡，您將在“日期”按鈕下看到選項。

但是，如果您選擇文本列，則日期按鈕下的選項將不再可用。

對於智能過濾，文本列有文本過濾器，日期列有日期過濾器。

對於下一列，將列名稱更改為Customer Key並將數據類型更改為Whole Number。

接下來，將 Channel、Currency Code 和 Warehouse Code 列的數據類型更改為文本。

然後，將Delivery Region Index的名稱更改為Delivery Region Key並將其數據類型更改為Whole Number。然後，對Product Description Index和Order Quantity列執行相同的操作。

最後，對於 Unit Price、Line Total 和 Unit Cost 列，將數據類型設置為Fixed Decimal Number。

使用 Power Query 語言流設置數據類型

由於列中的更改，已在 Applied Steps 窗格中創建了很多步驟。問題是由於多次執行相同類型的轉換，示例表查詢變得低效。這是你應該避免的事情。

為了提高查詢效率，請嘗試通過在創建另一個步驟之前將特定轉換應用於所有列來創建單個步驟。

複製示例表查詢並將其重命名為Best Practice。在 Applied Steps 窗格中，右鍵單擊在上一個查詢中執行的第一個轉換，然後選擇Delete Until End。然後，在刪除步驟對話框中確認刪除該步驟。

此外，這裡還有一些最佳實踐。第一件事是創建包含數據源位置的參數。這使得在更改文件名時更容易修復問題。

要創建參數，請單擊“主頁”選項卡中的“管理參數” ，然後選擇“新建參數”。

另一種方法是右鍵單擊“查詢”窗格並選擇“新建參數”。

之後，將出現一個管理參數對話框。將參數命名為FileLocation並將類型設置為文本。對於建議值，將其設置為值列表，以便您可以粘貼字符串並更改或添加多個位置，您可以在這些位置之間切換。

接下來，轉到您的文件資源管理器並選擇您的文件。複製路徑並粘貼參數。完成後，按OK。

返回 Best Practice 查詢並單擊 Applied Steps 窗格中的 Source 步驟。然後，使用FileLocation更改公式欄中的硬編碼文件路徑。

刪除不需要的列

刪除所有不必要的列以節省空間並提高性能。只帶上您需要的數據，因為添加表和列比刪除它們容易得多。

最簡單的方法是通過“主頁”選項卡中的“選擇列”步驟。如果單擊該按鈕，將出現一個對話框，讓您選擇要保留的列。您應該根據特定目的設計和塑造您的表格，以最好地適應您將要執行的分析。

從那裡，取消選擇表格中不需要的列。對於此示例表，OrderNumber 列的基數很高。最好取消選擇該列，因為它會影響文件大小和整體性能。

如果您不需要位置數據來分析示例查詢，最好也取消選擇Delivery Region Index。最後，由於表中已有單價和數量，因此不需要“行合計”列。

取消選擇列後，按OK。如果要更改列選擇，只需清除“應用步驟”窗格中步驟名稱旁邊的齒輪圖標。

為列分配適當的數據類型並限制行

接下來，為所有列分配數據類型。選擇所有列並單擊轉換選項卡中的檢測數據類型。

檢測數據類型根據對前 200 行的掃描自動檢測列的數據類型。因此，檢查並確保 Power Query 設置了正確的數據類型。

最好的做法是限制行數。如果您的財政年度從 7 月 1 日開始，您可以省略 6 月的數據或設置一個參數。

但是，如果您希望能夠更改LuckyTemplates 服務中的參數值，則有一個問題。發布報告後，您的參數值需要是文本類型或小數類型。

要處理該問題，請通過單擊 OrderDate 列中的下拉按鈕來創建數據過濾器。接下來，單擊Date Filters並選擇After。

在對話框中，將第一個參數更改為早於或等於，然後輸入日期。在此示例中，輸入的日期是 2014 年 7 月 1 日。

按OK後，過濾器將應用到您的表中。接下來，創建一個參數並將其命名為DatesFrom。將類型設置為文本並在當前值參數中輸入日期。

現在，回到最佳實踐查詢並將內在的#date替換為Date.From(DatesFrom)。

單擊沒有Date.From函數的複選標記將返回錯誤。這是因為 DatesFrom 是文本類型，而該字段具有日期類型。Date.From函數將文本轉換為日期。

重命名不會隱藏在數據模型中的所有列。名稱需要簡潔、自我描述和用戶友好。請記住，您正在為將要使用您的報告的人設計數據模型。

整合 Power Query 語言流程中的冗餘步驟

接下來要做的是合併冗餘步驟（例如，重命名、刪除和更改列數據類型）。此外，重新排序列等步驟是您在查找冗餘時應該注意的事情。

您加載到數據模型的表永遠不會顯示在您的報告中。這使得列順序無關緊要。

另一個最佳做法是在應用步驟窗格中重命名您的步驟。步驟的名稱是自描述的，並在M代碼中用作變量。

包含空格或特殊字符的名稱使用引號表示法書寫。這意味著名稱包含在一組雙引號中，並且它們前面有井號或井號，這使得 M 代碼難以閱讀。您可以省略空格或在它們之間放置下劃線。

通過在高級編輯器窗口中添加註釋來添加額外的文檔詳細信息也是 Power Query 中的最佳做法。您也可以在Step Property Description中執行此操作。當您將鼠標懸停在應用步驟窗格中帶有感嘆號的步驟上時，它們將顯示為工具提示註釋。

當您在一段時間後必須重新訪問文件時，知道為什麼在最初的開發中做出某種選擇是非常有幫助的。要添加文檔詳細信息，請右鍵單擊Applied Steps 窗格中的步驟並選擇Properties。

將出現一個步驟屬性對話框，您可以在其中寫下過濾或轉換的原因。

組織查詢以獲得更好的 Power Query 語言流

Power Query 中最常見的最佳做法之一是組織查詢。為參數、函數、暫存查詢和將加載到數據模型的查詢創建文件夾。在此示例中，選擇 FileLocation 和 DatesFrom 查詢並右鍵單擊它們。然後，選擇Move To Group並點擊New Group。

接下來，為所選查詢添加名稱，然後按OK。

對查詢進行分組後，您的查詢窗格將如下所示。

對於所有暫存查詢，請確保通過取消選中啟用負載來禁用負載。

本教程要討論的另一件事是語言流程。Applied Steps 窗格中的每個步驟都會轉換一個值，您可以在單擊它們時看到該值。

初始數據在導航步驟中出現並開始，並選擇了列。接下來，更改數據類型並設置日期範圍。列也被重新命名。

所有步驟都返回一個表類型值。如果打開“高級編輯器”窗口，您會看到一個let表達式和一個in子句。在它們之間，有一個步驟或變量名稱列表，其中包含分配給它們的表達式。

查詢返回in子句後面的任何內容，它指的是變量列表中的最後一步。然後M引擎將沿著依賴鏈從in子句返回，以消除任何不必要的東西，並在可能的情況下將轉換推回源。

Power Query 語言流程總結

如果查看公式，您可以看到在查詢中完成的步驟的流程。您還將看到用戶界面在每個步驟中使用的功能。

查詢中進行的第一步是列選擇。當使用用戶界面執行該步驟時，將調用Table.SelectColumns函數。作為它的第一個參數，它採用了一個表，該表引用了上一步的變量名稱。然後，它列出了所有選定的列名。

第二步通過調用Table.TransformColumnTypes函數轉換列類型。它的第一個參數稱為上一步的輸出。然後它列出了一組轉換列表。

第三步使用Table.SelectRows函數設置日期範圍過濾器。它以表類型查詢作為第一個參數。在此示例中，它引用了更改類型步驟的輸出。

最後一步使用Table.RenameColumns函數重命名了列。上一步的輸出用作其第一個參數。然後，它列出了一組重命名列表。

通過用戶界面應用的所有函數都以單詞Table開頭。他們都將表值作為第一個參數，然後轉換該值。

雖然代碼看起來是順序的，因為每個步驟都引用前一步，但順序並不是必需的。如果您移動一步，查詢仍會運行，因為M引擎始終遵循依賴鏈。

擁有用戶界面很方便，但它總是假設您要轉換先前轉換的結果。在大多數情況下，這可能是正確的，如果不是，您將不得不手動更新代碼中的引用值。

Power Query 數據類型和連接器
LuckyTemplates 數據集：類型和命名約定
數據加載和轉換最佳實踐

結論

數據轉換對於保持數據分組和組織是必要的。它使數據開發更快，因為您可以輕鬆地跟踪 Power Query 語言流中的問題並修改報表中的更改。

梅麗莎