我有一個包含此類資訊的巨大串列：
現在我想對這個串列進行分組/取消透視以獲得每個訂單號
- 基于鍵 1 = 70 和鍵 2 = 10 的最大銷售價值和
- 基于鍵 1 = 70 和鍵 2 = 20 的最大銷售價值
結果將是這樣的：
這如何通過使用 Power Query 實作？
uj5u.com熱心網友回復：
你可以這樣做：
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRMgURhhCWgVKsDkzcDEQYgQgTFHFzuHoLHOLmphjiRhATYeKGOOw1xGGvIQ57DXHYa4hiL1h9LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Number" = _t, #"Key 1" = _t, #"Key 2" = _t, #"Sales Value" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Number", Int64.Type}, {"Key 1", Int64.Type}, {"Key 2", Int64.Type}, {"Sales Value", Int64.Type}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Key 1", type text}, {"Key 2", type text}}, "de-CH"),{"Key 1", "Key 2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
#"Filtered Rows" = Table.SelectRows(#"Merged Columns", each ([Merged] = "7010" or [Merged] = "7020")),
#"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Merged]), "Merged", "Sales Value", List.Max)
in
#"Pivoted Column"
輸出：
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/415478.html
標籤：