Power Query 中的資料整理轉換函數

適用於： Azure Data Factory Azure Synapse Analytics

Azure Data Factory 的資料整理讓你能在雲端規模進行無程式碼的敏捷資料準備與整理，方法是將 Power Query M 腳本轉換成Data Flow腳本。 ADF 整合 Power Query Online，並透過 Spark 執行資料流基礎設施，使 Power Query M 函式可用於資料整理。

目前並非所有 Power Query M 函式都支援資料整理，儘管在撰寫過程中可用。 建置的交互式資料時，如果不支援某個函式，系統會提示您並出現下列錯誤訊息：

UserQuery : Expression.Error: The transformation logic is not supported as it requires dynamic access to rows of data, which cannot be scaled out.

以下是支援的 Power Query M 函式清單。

資料行管理

• 選取範圍：Table.SelectColumns
• 移除：Table.RemoveColumns
• 重新命名：Table.RenameColumns、Table.PrefixColumns、Table.TransformColumnNames
• 重新排序：Table.ReorderColumns

資料列篩選

使用 M 函式 Table.SelectRows 來篩選下列條件：

• 等式和不等式
• 數值、文字和日期比較 (但不是 DateTime)
• 數值資訊，例如 Number.IsEven/Odd
• 使用 Text.Contains、Text.StartsWith 或 Text.EndsWith 的文字內含項目
• 日期範圍包含所有 'IsIn' Date 函式)
• 這些項會使用和、或，不是條件組合

新增和轉換資料行

下列 M 函式會新增或轉換資料行：Table.AddColumn、Table.TransformColumns、Table.ReplaceValue、Table.DuplicateColumn。 以下是支援的轉換函式。

• 數值算術
• 文字串連
• 日期和時間算術 (算術運算子、Date.AddDays、Date.AddMonths、Date.AddQuarters、Date.AddWeeks、Date.AddYears)
• 持續時間可用於日期和時間算術，但必須在寫入接收之前轉換為另一個類型 (算術運算子、#duration、Duration.Days、Duration.Hours、Duration.Minutes、Duration.Seconds、Duration.TotalDays、Duration.TotalHours、Duration.TotalMinutes、Duration.TotalSeconds)
• 大部分的標準、科學和三角數值函式（除了 Number.Factorial、Number.Permutations 和 Number.Combinations 之外，Operations、Rounding 和 Trigonometry 下的所有函式）
• 取代 (Replacer.ReplaceText、Replacer.ReplaceValue、Text.Replace、Text.Remove)
• 位置文字擷取 (Text.PositionOf、Text.Length、Text.Start、Text.End、Text.Middle、Text.ReplaceRange、Text.RemoveRange)
• 基本文字格式 (Text.Lower、Text.Upper、Text.Trim/Start/End、Text.PadStart/End、Text.Reverse)
• 日期/時間函式（Date.Day、Date.Month、Date.Year Time.Hour、Time.Minute、Time.second、Date.DayOfWeek、Date.DayOfYear、Date.DaysInMonth）
• 如果運算式 (但分支必須有相符的類型)
• 資料列篩選作為邏輯資料行
• Number、text、logical、date 和 datetime 常數

合併/聯結資料表

• Power Query 會產生巢狀連接（Table.NestedJoin;使用者也可以手動寫入 Table.AddJoinColumn）。 然後，使用者必須將巢狀聯結資料行展開至非巢狀聯結 (Table.ExpandTableColumn，在任何其他內容中不支援)。
• 您可以直接撰寫 M 函式 Table.Join 以避免需要額外的展開步驟，但使用者必須確保聯結資料表之間沒有重複的資料行名稱
• 支援的聯結種類：Inner、LeftOuter、RightOuter、FullOuter
• Value.Equals 和 Value.NullableEquals 都支援做為索引鍵相等比較子

分組依據

使用 Table.Group 來彙總值。

• 必須搭配彙總函式使用
• 支援的彙總函式：List.Sum、List.Count、List.Average、List.Min、List.Max、List.StandardDeviation、List.First、List.Last

排序

使用 Table.Sort 來排序值。

減少資料列

保留並移除最前面的項目、保留範圍 (對應的 M 函式，僅支援計數，不支援條件：Table.FirstN、Table.Skip、Table.RemoveFirstN、Table.Range、Table.MinN、Table.MaxN)

已知不支援的函式

M 指令碼因應措施

SplitColumn

以下列出依長度和位置分割的替代方式

• Table.AddColumn（來源，「首位字元」，每個 Text.Start（[Email]， 7），輸入文字）
• Table.AddColumn（#“插入的首位字元”、“文字範圍”，每個 Text.Middle（[電子郵件]， 4， 9），輸入文字）

您可以從功能區的 [擷取] 選項存取此選項

Table.CombineColumns

• Table.AddColumn（RemoveEmailColumn， “Name”， 每個 [FirstName] & “ ” & [LastName]）

樞紐

• 從 PQ 編輯器選取 [樞紐轉換]，並選取樞紐資料行

• 接下來，選取值資料行和彙總函式

• 按一下 [確定] 時，您會看到編輯器中的資料更新為樞紐值
• 您也會看到一則警告訊息，指出轉換可能不受支援
• 若要修正此警告，請使用 PQ 編輯器手動展開樞紐清單
• 從色帶中選擇 進階編輯器 選項
• 手動展開樞紐值清單
• 將 List.Distinct() 取代為如下所示的值清單：

#"Pivoted column" = Table.Pivot(Table.TransformColumnTypes(#"Changed column type 1", {{"genres", type text}}), {"Drama", "Horror", "Comedy", "Musical", "Documentary"}, "genres", "Rating", List.Average)
in
  #"Pivoted column"

格式化日期/時間資料行

使用 Power Query ADF 時，請依照以下設定設定格式。

1. 在 Power Query UI 中選擇欄位，並選擇變更類型 >日期/時間
2. 您會看到警告訊息
3. 打開進階編輯器，將 TransformColumnTypes 改為 TransformColumns。 根據輸入資料指定格式和文化特性。

#"Changed column type 1" = Table.TransformColumns(#"Duplicated column", {{"start - Copy", each DateTime.FromText(_, [Format = "yyyy-MM-dd HH:mm:ss", Culture = "en-us"]), type datetime}})

相關內容

學習如何在 ADF 建立資料整理Power Query。