英文:
Power Query - Columns as separate unrelated list (from unpivoted data)
问题
如何将一个简单的未旋转的列表转换成如下的列表，其中各行之间没有关联，每列几乎是一个独立的列表？旋转操作不适用 - 这是否可能？
| 艺术 | 生物学 | 英语 |
|---|---|---|
| Emma | Fred | John |
| Sam | John | 无 |
| 无 | Mike | 无 |
谢谢。
英文:
If I have a simple unpivoted list like this:
| Names | Subjects |
|---|---|
| John | English |
| Sam | Art |
| Fred | Biology |
| John | Biology |
| Emma | Art |
| Mike | Biology |
How can I create a list like this where the rows are unrelated to each other and each column is almost a separate list? Pivoting doesn't work - is this possible?
| Art | Biology | English |
|---|---|---|
| Emma | Fred | John |
| Sam | John | null |
| null | Mike | null |
Thanks
答案1
得分: 5
你需要在旋转之前对数据进行分组并添加索引。
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Names", type text}, {"Subjects", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Subjects"}, {{"All", each _, type table [Names=nullable text, Subjects=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All], "Index")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"All"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Names", "Index"}, {"Names", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Subjects]), "Subjects", "Names"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
#"Removed Columns1"
图片链接：
[链接1]: https://i.stack.imgur.com/S8bce.png
[链接2]: https://i.stack.imgur.com/Z1zai.png
[链接3]: https://i.stack.imgur.com/a8d2D.png
[链接4]: https://i.stack.imgur.com/TvlzZ.png
[链接5]: https://i.stack.imgur.com/C0j3L.png
[链接6]: https://i.stack.imgur.com/JrBPi.png
英文:
You need to group and add an index before pivoting.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Names", type text}, {"Subjects", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Subjects"}, {{"All", each _, type table [Names=nullable text, Subjects=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All], "Index")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"All"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Names", "Index"}, {"Names", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Subjects]), "Subjects", "Names"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
#"Removed Columns1"
答案2
得分: 3
以下是您要的代码部分的翻译：
只是作为一个练习，不进行分组：
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Ans = let s1=Table.Column(Source, "Subjects"),
s2=List.Sort(List.Distinct(s1)),
n1=Table.Column(Source, "Names"),
x=List.Numbers(0,List.Count(n1)),
n2=List.Transform(s2,(y)=>List.Accumulate(x,{},(s,c)=>if s1{c}=y then List.Combine({s,{n1{c}}}) else s)),
m=List.Numbers(0,List.Max(List.Transform(n2,(z)=>List.Count(z)))),
r1=List.Transform(m,(q)=>Record.FromList(List.Accumulate(s2,{},(s,c)=>List.Combine({s,{try n2{List.PositionOf(s2,c)}{q} otherwise null}})),s2)),
t1=Table.FromRecords(r1)
in t1
in
Ans
请注意，我已经将HTML实体编码（如"）转换为原始字符。
英文:
Just as an exercise, without grouping:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Ans = let s1=Table.Column(Source, "Subjects"),
s2=List.Sort(List.Distinct(s1)),
n1=Table.Column(Source, "Names"),
x=List.Numbers(0,List.Count(n1)),
n2=List.Transform(s2,(y)=>List.Accumulate(x,{},(s,c)=>if s1{c}=y then List.Combine({s,{n1{c}}}) else s)),
m=List.Numbers(0,List.Max(List.Transform(n2,(z)=>List.Count(z)))),
r1=List.Transform(m,(q)=>Record.FromList(List.Accumulate(s2,{},(s,c)=>List.Combine({s,{try n2{List.PositionOf(s2,c)}{q} otherwise null}})),s2)),
t1=Table.FromRecords(r1)
in t1
in
Ans
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论