Hello,
In excel I have this table (it is just an example) :
Table called T_DATA
idlParentId
dtm|fact
dtm|dim
fact|raw1
dim|raw2
dtm2|raw3
Etc…
I want to flat the hierarchy based on a value the user populate in cella D1 for example
For example if in D1 I populate : dtm
the result expected would be :
(row 1) : dtm>fact>raw1
(row 2) : dtm>dim>raw2
if in A1 I populate : Dtm2
the result expected would be :
(row 1) : dtm2>raw3
So in summary, each path found must be concatened (separated by a '>'),
and there is 1 path by row.
I managed to do it and it works for the 1st path only, with the following recursive function/formula called GetPath:
=LAMBDA(start,
LET(
nextItems,FILTER(T_DATA[ParentId];T_DATA[Id]=start);
IF(nextItem="";start;start&">"&GetPath(nextItem))
)
)
and by using it like this : GetPath(D1)
But I cannot figure out a way to get ALL paths based on the criteria searched in A1
I tried other options found on internet, but the formula returns #N/A each time
See attached file.
If anyone can help me, it would be very appreciated.
In excel I have this table (it is just an example) :
Table called T_DATA
idlParentId
dtm|fact
dtm|dim
fact|raw1
dim|raw2
dtm2|raw3
Etc…
I want to flat the hierarchy based on a value the user populate in cella D1 for example
For example if in D1 I populate : dtm
the result expected would be :
(row 1) : dtm>fact>raw1
(row 2) : dtm>dim>raw2
if in A1 I populate : Dtm2
the result expected would be :
(row 1) : dtm2>raw3
So in summary, each path found must be concatened (separated by a '>'),
and there is 1 path by row.
I managed to do it and it works for the 1st path only, with the following recursive function/formula called GetPath:
=LAMBDA(start,
LET(
nextItems,FILTER(T_DATA[ParentId];T_DATA[Id]=start);
IF(nextItem="";start;start&">"&GetPath(nextItem))
)
)
and by using it like this : GetPath(D1)
But I cannot figure out a way to get ALL paths based on the criteria searched in A1
I tried other options found on internet, but the formula returns #N/A each time
See attached file.
If anyone can help me, it would be very appreciated.