英文:
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中，你可以尝试以下操作：
letSource = 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", eachTable.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 trylet 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", eachTable.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"[![enter image description here][1]][1][1]: https://i.stack.imgur.com/bTmUl.png</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论