Excel / Powerquery:重新排列和转置表格的一部分

huangapple go评论67阅读模式
英文:

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.

Excel / Powerquery:重新排列和转置表格的一部分

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.

Excel / Powerquery:重新排列和转置表格的一部分

It can be simple excel or powerquery, even as VB script please. Anything that can do the trick.

Thanks

答案1

得分: 1

你可以在Power Query中执行以下操作:

  1. 对除了Customer和Month之外的列进行解标列操作

  2. 使用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:

  1. unpivot columns other than Customer and Month

  2. 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"

huangapple
  • 本文由 发表于 2020年1月3日 17:10:40
  • 转载请务必保留本文链接:https://go.coder-hub.com/59575764.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定