Power Query 子层次结构 [英] Power Query Child hierarcy
问题描述
我有一个问题需要在电源查询中解决.
I have a problem I need to solve in power query.
我有包含名称、ID 和父 ID 列的表
I have table containing columns Name, ID and Parent ID
Name ID Parent
A 1
B 2 1
C 3 1
D 4 2
E 5 2
我需要转换此表,以便为该行的每个子项(和原始)获得一行.在本例中,这将产生 11 行.
I need to transform this table so that I get one row for each child (and orginal) that row has. In this example this would yield 11 rows.
Name ID
A 1
A 2
A 3
A 4
A 5
B 2
B 4
B 5
C 3
etc.
我知道这是通过某种连接完成的,但感觉就像一个循环,我不知道如何在 Power Query 中执行循环.
I know this is done with some sort of joining but it feels like a loop and I don't know how to do loops in Power Query.
推荐答案
试试这个,不一定对,请查一下.有没有人有更好的解决方案?
Try this,not necessarily right,please check it.Does anyone else have a better solution?
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Top = Table.SelectRows(Source, each ([Parent] = null)),
Path = Table.AddColumn(Top, "Path", each #table({"Name","ID"},{{[Name],[ID]}})),
Loop = Table.Combine(List.Generate(()=>
Path,
each not Table.IsEmpty(_),
each [
a = Table.NestedJoin(Source,{"Parent"},_,{"ID"},"join",JoinKind.Inner),
b = Table.ExpandTableColumn(a, "join", {"Path"}, {"Pre_Path"}),
c = Table.AddColumn(b,"Path", each [Pre_Path]&Table.Group([Pre_Path],"Name",{"ID",(x)=>[ID]})&#table({"Name","ID"},{{[Name],[ID]}}))
][c]
)),
Result = Table.Distinct(Table.Combine(Loop[Path])),
Sort = Table.Buffer(Table.Sort(Result,{{"Name",0}, {"ID",0}}))
in
Sort
Power Pivot 简单多了,你可以用PATH 来解决Parent-Child Hierarchy 的问题.
Power Pivot is much easier,you can use PATH to solve the problem of Parent-Child Hierarchy.
这篇关于Power Query 子层次结构的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!