資料整頓Power Query中的轉換函式
適用於：
Azure Data Factory 
Azure Synapse Analytics
Azure Data Factory中的資料整頓可讓您透過將Power Query腳本轉譯成資料Flow M 腳本，以雲端規模執行無程式碼的敏捷式資料準備和整頓。 ADF 會與Power Query Online整合，並使用資料流程 Spark 基礎結構，讓Power Query M 函式可供透過 Spark 執行進行資料整頓。
目前並非所有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/奇數
- 使用Text.Contains、Text.StartsWith或Text.EndWith 的文字內含專案
- 日期範圍，包括所有 'IsIn' 日期函 式)
- 這些使用 和、或或不是條件的組合
新增和轉換資料行
下列 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.Combination) s 以外，大部分的標準、科學和三角數值函式 (Operations、 Rounding和 Trigonometry下 的所有函式
- Replace (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 時設定日期/時間格式，請遵循這些集合來設定格式。
- 選取Power Query UI 中的資料行，然後選擇 [變更類型 > 日期/時間]
- 您會看到警告訊息
- 開啟進階編輯器，並將 變更
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}})