Forum Discussion
Hierarchy processing in PowerQuery
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;