Forum Discussion
Hierarchy processing in PowerQuery
Hi g0ldfinch
(Interesting scenario) Attached is a way to do it by merging on [ID] & [PARENT] after splitting the source table in 2 tables:
- 1st: [PARENT] = 0
- 2nd: [PARENT] <> 0
Thank you, Lorenzo , but 2 levels were provided only to simplify the example. There may be up to 5 levels (i hope:), so the splitting strategy won't work... unless i create (dynamically) as many source tables as i have levels, and then (dynamically, again) do joins and appends... i think recursion is much simpler in implementation (:
That's why i mentioned recursion, but the latter isn't that good usually, unless Excel knows how to optimise tail recursions.
SQL:
select * from bom_full_path;
create temp table aa as
with recursive under_alice as (select ID,PARENT,TEXT,TEXT path from bom_full_path where PARENT like 0 union
select x.ID,x.PARENT,x.TEXT,under_alice.path||'|'||x.TEXT from bom_full_path x join under_alice on x.PARENT=under_alice.ID)
select * from under_alice;
select *,iif(PARENT=0,'',path) path from aa;
 
Hi g0ldfinch
There may be up to 5 levels
It would have been good you mentioned this in your initial post 🙂
Maybe you can mix recursion and the merging approach I shared. Just an idea as I haven't really thought if this would do what you want
Do you feel confortable having a look at this (not sure I'll have time today)?
In the meantime could you post a sample with say 3-4 levels?
