英文:
Excel / Powerquery: reshape and transpose part of a table
问题
I have a table like this that i built using powerquery with lots of data from different places.
现在我有一个表格,使用Power Query 从不同地方收集了大量数据。
Now i'm stuck trying to transpose part of this while leaving the main column as is.
I need to change this to the below format for further processing by a different script i cannot change.
现在我卡在尝试转置其中的一部分,同时保持主列不变。我需要将其更改为下面的格式,以供不同的脚本进一步处理,而我无法更改它。
It can be simple excel or powerquery, even as VB script please. Anything that can do the trick.
可以是简单的Excel或Power Query,甚至是VB脚本,请。只要能解决问题的方法。
英文:
I have a table like this that i built using powerquery with lots of data from different places.
Now i'm stuck trying to transpose part of this while leaving the main column as is.
I need to change this to the below format for further processing by a different script i cannot change.
It can be simple excel or powerquery, even as VB script please. Anything that can do the trick.
Thanks
答案1
得分: 1
你可以在Power Query中执行以下操作:
-
对除了Customer和Month之外的列进行解标列操作
-
使用Value作为值列,对Month进行标列操作
就像下面的代码一样:
let
Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Month", type text}, {"Revenue", type number}, {"Product 1", type number}, {"Product 2", type number}, {"Product 3", type number}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Customer", "Month"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Month]), "Month", "Value", List.Sum)
in
#"Pivoted Column"
英文:
you can do the following in Power Query:
-
unpivot columns other than Customer and Month
-
pivot Month with Value as value column
as in the code below
let
Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Month", type text}, {"Revenue", type number}, {"Product 1", type number}, {"Product 2", type number}, {"Product 3", type number}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Customer", "Month"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Month]), "Month", "Value", List.Sum)
in
#"Pivoted Column"
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论