英文:

data horizontally power query

问题

我有以下的数据集示例，我想将它们横向排列。我尝试按“sol_ped”、“pos”和“name”分组，然后使用Table.Column创建一个自定义列，但它只允许我添加一个字段，例如“prec”，而我需要两个或更多字段。
有什么办法可以做到吗？

示例数据集

sol_ped	pos	name	prec	cat
11	1	one	11.01	a
11	1	one	14.00	b
11	1	one	7.00	c
11	2	two	8.12	d

期望的结果

sol_ped	pos	name	prec1	cat1	prec2	cat2	prec3	cat3	prec4	cat4
11	1	one	11.01	a	14.00	b	7.00	c	0.00	na
11	2	two	8.12	d	0.00	na	0.00	na	0.00	na

英文:

I have the following example of a data set, I want to have them horizontally. I tried grouping by "sol_ped", "pos", and "name" , then creating a custom column with Table.Column but it only lets me add one field for example "prec" and I need two or more fields.
Any idea how I can do?

sample dataset

sol_ped	pos	name	prec	cat
11	1	one	11.01	a
11	1	one	14.00	b
11	1	one	7.00	c
11	2	two	8.12	d

desired result

sol_ped	pos	name	prec1	cat1	prec2	cat2	prec3	cat3	prec4	cat4
11	1	one	11.01	a	14.00	b	7.00	c	0.00	na
11	2	two	18.02	d	18.12	na	na	na	na	na

答案1

得分: 0

在Power Query中，你可以尝试以下操作：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"sol_ped", "pos", "name"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"sol_ped", "pos", "name"}, {
        {"data", each
            Table.AddColumn(
                Table.AddColumn(
                    Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type),
                    "Count", each Number.IntegerDivide([Index], 2), Int64.Type),
                "MergeName", each [Attribute] & Text.From([Count] + 1))
        , type table }}),
    #"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Value", "MergeName"}, {"Value", "MergeName"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded data", List.Distinct(#"Expanded data"[MergeName]), "MergeName", "Value"),
    #"Change Type" = Table.TransformColumnTypes(#"Pivoted Column", List.Transform(List.Select(Table.ColumnNames(#"Pivoted Column"), each Text.StartsWith(_, "prec")), each {_, type number})
in
    #"Change Type"

请注意，这是一段Power Query的代码，用于对数据进行转换和处理。

<details>
<summary>英文:</summary>

In powerquery you can try

    let  Source = Excel.CurrentWorkbook(){[Name=&quot;Table1&quot;]}[Content],
    #&quot;Unpivoted Other Columns&quot; = Table.UnpivotOtherColumns(Source, {&quot;sol_ped&quot;, &quot;pos&quot;, &quot;name&quot;}, &quot;Attribute&quot;, &quot;Value&quot;),
    #&quot;Grouped Rows&quot; = Table.Group(#&quot;Unpivoted Other Columns&quot;, {&quot;sol_ped&quot;, &quot;pos&quot;, &quot;name&quot;}, {
        {&quot;data&quot;, each     
            Table.AddColumn(
                Table.AddColumn(
                    Table.AddIndexColumn(_, &quot;Index&quot;, 0, 1, Int64.Type)
                , &quot;Count&quot;, each Number.IntegerDivide([Index], 2), Int64.Type)
            ,&quot;MergeName&quot;, each [Attribute]&amp;Text.From([Count]+1 ))
    , type table }}),
    #&quot;Expanded data&quot; = Table.ExpandTableColumn(#&quot;Grouped Rows&quot;, &quot;data&quot;, {&quot;Value&quot;, &quot;MergeName&quot;}, {&quot;Value&quot;, &quot;MergeName&quot;}),
    #&quot;Pivoted Column&quot; = Table.Pivot(#&quot;Expanded data&quot;, List.Distinct(#&quot;Expanded data&quot;[MergeName]), &quot;MergeName&quot;, &quot;Value&quot;),
    #&quot;Change Type&quot;= Table.TransformColumnTypes(#&quot;Pivoted Column&quot;,List.Transform(List.Select(Table.ColumnNames(#&quot;Pivoted Column&quot;), each Text.StartsWith(_,&quot;prec&quot;)),each {_, type number}))
    in #&quot;Change Type&quot;
[![enter image description here][1]][1]


  [1]: https://i.stack.imgur.com/bTmUl.png

</details>