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 leading to the current node. E.g.
| ID | PARENT | TEXT | path |
| 1 | 0 | Application Platform | |
| 2 | 1 | Supporting components for AP | Application Platform | Supporting components for AP |
| 3 | 1 | Directory Structure for AP | Application Platform | Directory Structure for AP |
What is the optimal way to do it? Looks like i need a recursive function, but, without an index on the ID column this will be slow...
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;
 
