Excel PowerQuery:如何取消透视表或将大表转置为可读格式以进行分析 [英] Excel PowerQuery: How to unpivot or transpose a huge table into a readable format for analysis

问题描述

我有这张看起来与此类似的表:

I have this table that looks similar to this:

我想对其进行转换，使其看起来像这样:

And I want to transform it so that it looks like this:

此想法是取消旋转(或转置)表格，以便可以将其输入其他BI工具中，并便于分析.

The idea is to unpivot( or transpose) the table so that it can be feed into other BI tools, and be readable for analysis.

我大约有20张这样的桌子，上面有100多个列，因此当然几乎不可能手动进行.

I have around 20 tables like this with 100+ cols, so of course to do it manually is nearly impossible.

如何使用PowerQuery完成此操作?我曾尝试使用unpivot功能，但由于显示了NYC1，NYC2等而被卡住.VBA，宏也无法正常工作.任何其他建议，我们都感激不尽，但我现在不知所措.救命！

How do I get this done with PowerQuery? I have tried using unpivot feature, but I am stuck as it displayed NYC1, NYC2, etc. VBA, macros don't work also. Any other suggestions are appreciated, but I am at my wits end now. Help!

推荐答案

在加载到PowerQuery中之前，请使用定界符(空格)将标题连接到[程序名称]后的空白行.如果使用office365，则可以使用TEXTJOIN函数来执行此操作.结果看起来像这样(我没有复制您的所有数据):

Before loading into PowerQuery, concatenate the headers to the empty row after [Programe Name] using a delimiter (space). You can use the TEXTJOIN function to do this if you use office365. The result looks something like this (I did not copy all your data):

将此范围导入PowerQuery并执行以下步骤(请勿选中my table has headers复选框)

Import this range into PowerQuery and perform the following steps (Do not check the my table has headers checkbox)

1. 删除前2行(首页"标签>删除行")
2. 将第一行用作标题(主页"标签>将第一行用作标题")
3. 选择第一列
4. 取消透视其他列(转换"选项卡上的下拉菜单取消透视列)
5. 用定界符(空格)分隔[属性]列(主页"选项卡>分隔列")
6. 更改列名
7. 将城市列移动到左侧(右键单击列>移动>左边)

脚本如下:

let
    Source = Excel.CurrentWorkbook(){[Name="table"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",2),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Program Name", type text}, {"NY Budget", Int64.Type}, {"NY Revenue", Int64.Type}, {"NY Cost", Int64.Type}, {"NY Margin", Int64.Type}, {"LA Budget", Int64.Type}, {"LA Revenue", Int64.Type}, {"LA Cost", Int64.Type}, {"LA Margin", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Program Name"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Attribute.1", "City"}, {"Attribute.2", "Description"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"City", "Program Name", "Description", "Value"})
in
    #"Reordered Columns"

这是结果(在Power Query编辑器中)

And this is the result (in the Power Query Editor)

这篇关于Excel PowerQuery:如何取消透视表或将大表转置为可读格式以进行分析的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！