Power Query 中針對資料整頓的轉換函式

適用於：Azure Data Factory Azure Synapse Analytics

提示

試用 Microsoft Fabric 中的 Data Factory，這是適用於企業的全方位分析解決方案。 Microsoft Fabric 涵蓋從資料移動到資料科學、即時分析、商業智慧和報告的所有項目。 了解如何免費開始新的試用！

Azure Data Factory 中的資料整頓可讓您將 Power Query M 指令碼轉譯成資料流程指令碼，以雲端規模執行無程式碼的敏捷式資料準備和整頓。 ADF 與 Power Query Online 整合，並使用資料流程 Spark 基礎結構，透過 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)
• 大部分的標準、科學和三角數值函式 (Operations、Rounding 和 Trigonometry 下的所有函式，Number.Factorial、Number.Permutations 和 Number.Combinations 除外)
• 取代 (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.YearTime.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)

已知不支援的函式

函式	狀態
Table.PromoteHeaders	不支援。 在資料集中設定「第一個資料列為標頭」，即可達成相同的結果。
Table.CombineColumns	這是不直接支援但可藉由新增串連兩個指定資料行的新資料行來達成的常見案例。 例如，Table.AddColumn(RemoveEmailColumn, "Name", each [FirstName] & " " & [LastName])
Table.TransformColumnTypes	在大部分情況下都支援。 不支援下列案例：將字串轉換成貨幣類型、將字串轉換成時間類型、將字串轉換成百分比類型，以及使用地區設定進行轉換。
Table.NestedJoin	只是執行聯結，將會產生驗證錯誤。 必須展開資料行，它才能運作。
Table.RemoveLastN	不支援移除底部資料列。
Table.RowCount	不支援，但可以藉由新增包含值 1 的自訂資料行來達成，然後使用 List.Sum 彙總該資料行。 支援 Table.Group。
資料列層級錯誤處理	目前不支援資料列層級錯誤處理。 例如，若要從資料行篩選掉非數值，其中一種方法是將文字資料行轉換成數值。 無法轉換的每個儲存格都會處於錯誤狀態，且必須經過篩選。 此案例在向外延展 M 中不可行。
Table.Transpose	不支援

M 指令碼因應措施

SplitColumn

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

• Table.AddColumn(Source, "First characters", each Text.Start([Email], 7), type text)
• Table.AddColumn(#"Inserted first characters", "Text range", each Text.Middle([Email], 4, 9), type text)

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

Table.CombineColumns

• Table.AddColumn(RemoveEmailColumn, "Name", each [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。