Forum Discussion
Hierarchy processing in PowerQuery
I need to process a hierarchy with the following columns: ID, PARENT, and TEXT. My goal is for any given node except the top one to display concatenation of the the text fields to show the path leadi...
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?
