在PowerQuery中使用多個條件透視列-有解無憂

我有以下源資料 -

在 Power Query 中使用多個條件透視列

我正在嘗試將其轉換為 -

在 Power Query 中使用多個條件透視列

我已經嘗試在轉換選項卡中旋轉型別列，但這會導致每個 SKU 的多行，在下一行中具有長度、高度和寬度的值。

在 Power Query 中使用多個條件透視列

如何透視資料，以便將所有標題作為列名，并將單位合并到每個標題的值中？

uj5u.com熱心網友回復：

從excel中的這個表開始，命名為Table1：

在 Power Query 中使用多個條件透視列

我用這個M碼...

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SKU", type text}, {"Type", type text}, {"Value", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Type"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 3), type number),
    #"Added Custom" = Table.AddColumn(#"Inserted Modulo", "Custom", each if Type.Is(Value.Type([Value]),type number) then Text.From([Value]) & " " & #"Inserted Modulo"{[Index]-1}[Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Modulo] = 0)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Modulo"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Value]), "Value", "Custom")
in
    #"Pivoted Column"

得到這個結果：

在 Power Query 中使用多個條件透視列

uj5u.com熱心網友回復：

試試這個

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "IsHeader", each if [Type]="Header" then [Index] else null),
#"Added Custom3" = Table.AddColumn(#"Added Custom", "Custom2", each if [Type]="Header" then [Value] else null),
#"Filled Down" = Table.FillDown(#"Added Custom3",{"IsHeader", "Custom2"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Type] <> "Header")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Type]), "Type", "Value"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Pivoted Column", {{"Value", type text}}, "en-US"),{"Value", "Unit"},Combiner.CombineTextByDelimiter( " ", QuoteStyle.None),"Merged"),
#"Removed Columns1" = Table.RemoveColumns(#"Merged Columns",{"IsHeader"}),
#"Pivoted Column1" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Custom2]), "Custom2", "Merged")
in  #"Pivoted Column1"

轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/383983.html

標籤：擅长枢电源查询

上一篇：如何在excel中創建增量順序拖拽公式，如AT3、AU3、AV3、AW3、

下一篇：將Excel十進制日期時間轉換為time.Time