☰

在 Power Query 中将一个表中的列合并到一列中

tag-icon • tag-icon power-query
在 Power Query 中将一个表中的列合并到一列中

我有一张表格（示例）。X - 一些值。

数字 A1 A2 A3 B1 B2 B3 C1 C2 C3

1 xxxxxxxxx

2 xxxxxxxxx

3 xxxxxxxxx

我需要制作这样的表格。希望您了解我需要什么。

数值 1 数值 2 数值 3

1 A1 B1 C1

1 A2 B2 C2

1 A3 B3 C3

2 A1 ...

2 A2 ...

2 A3 ...

3 A1 ...

答案1

所需的关键步骤是：

  1. 选择 Num 列并选择逆透视其他列
  2. 添加步骤以将结果属性列（以前称为列标题）拆分为 2 列
  3. 在属性的第一个字符的列上进行透视，选择“值”作为“值列”，选择“不聚合”作为聚合值函数。

这是一个测试查询（包括示例数据），以便您可以完成以下步骤：

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hc6xCcAwDETRVYTrELA0ge/6LGAyQsrsH/00Bw/7G+89rveJNWNlrArNUIYqPMMZrnEfe/TpPLnCVI+goKChoZt/kiRJwrs9goKChoad5J8USZEUCZ9hoKChYSf9sfsD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}, {"Column1.7", type text}, {"Column1.8", type text}, {"Column1.9", type text}, {"Column1.10", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type1", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Num", Int64.Type}, {"A1", type text}, {"A2", type text}, {"A3", type text}, {"B1", type text}, {"B2", type text}, {"B3", type text}, {"C1", type text}, {"C2", type text}, {"C3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {"Num"}, "Attribute", "Value"),
    #"Inserted Text Range" = Table.AddColumn(#"Unpivoted Other Columns", "Text Range", each Text.Middle([Attribute], 1, 1), type text),
    #"Inserted First Characters" = Table.AddColumn(#"Inserted Text Range", "First Characters", each Text.Start([Attribute], 1), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted First Characters",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"First Characters"]), "First Characters", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Text Range"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"A", "Value1"}, {"B", "Value2"}, {"C", "Value3"}})
in
    #"Renamed Columns"

相关内容