☰

如何将 Excel 表从许多重复行转置为唯一列？（最好在 PowerQuery 中）

tag-icon • tag-icon microsoft-excel power-query
如何将 Excel 表从许多重复行转置为唯一列？（最好在 PowerQuery 中）

我到处都找过了，也尝试了很多次，看来大多数人都不需要解决这个问题:-(我有一张有两列的表格。A 列（标签）有 4 个不同的值，以这种模式一遍又一遍重复。这些将成为我独特的列名，B 列（值）具有与这些标签匹配的值。

示例表：
源表

现在我只想创建一个如下所示的表格：（TAG 行中的每个不同值都将成为列标题，而 VALUE 列中的所有值都将成为这些新列下方的单独值）

期望目标表：
期望目标表

当我导入数据来源的 CSV 时，我最好在 PowerQuery 中完成所有这些转置，但稍后在某些范围/表格操作中执行此操作也可以。

任何帮助将非常感激 ：-）

答案1

在 Power Query 中，您将对 TAG 列进行透视，并以 VALUE 列作为值和no aggregation。

但是，如果您在数据集上执行此操作，则会返回错误值，因为同一个 Pivot 值有多个条目。

可以通过添加索引列并按顺序对每个单独的标签条目进行编号来避免这种情况。

为了做到这一点，并且允许标签不总是按照相同的顺序排列，您可以

  • 按标签分组
  • 为每个子表添加索引列
  • 展开表格
  • 以 TAG 为中心，无聚合
  • 删除索引列

在下面的代码中，检查注释和应用步骤以便更好地理解。

如果此方法在您的数据集上太慢，则可以发布一个可能执行得更快的自定义函数。

let

//replace next lines to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TAG", type text}, {"VALUE", type text}}),

//Group By Tage
    #"Grouped Rows" = Table.Group(#"Changed Type", {"TAG"}, {

        //add an Index column to each subtable
        {"with Index", each Table.AddIndexColumn(_,"Index",0,1), type table [TAG=nullable text, VALUE=nullable text, Index=Int64.Type]}}),

//Expand the sub tables
    #"Expanded with Index" = Table.ExpandTableColumn(#"Grouped Rows", "with Index", {"VALUE", "Index"}),

//Pivot with no aggregation
    #"Pivoted Column" = Table.Pivot(#"Expanded with Index", List.Distinct(#"Expanded with Index"[TAG]), "TAG", "VALUE"),

//Remove the Index column
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

在此处输入图片描述

编辑以显示用于执行非聚合数据透视的自定义函数

自定义函数
粘贴到空白查询中并按照代码注释重命名

//credit: Cam Wallace  https://www.dingbatdata.com/2018/03/08/non-aggregate-pivot-with-multiple-rows-in-powerquery/

//Rename:  fnPivotAll 

(Source as table,
    ColToPivot as text,
    ColForValues as text)=> 

let
     PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))),
     #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _),
 
    TableFromRecordOfLists = (rec as record, fieldnames as list) =>
    
    let
        PartialRecord = Record.SelectFields(rec,fieldnames),
        RecordToList = Record.ToList(PartialRecord),
        Table = Table.FromColumns(RecordToList,fieldnames)
    in
        Table,
 
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)),
    #"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames),
    #"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames)
in
    #"Expanded Values"

主要代码

let

//replace next lines to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TAG", type text}, {"VALUE", type text}}),

//call custom pivot function
    pivot = fnPivotAll(#"Changed Type","TAG","VALUE")
in
    pivot

相关内容