CODEWITHSUNDEEP
excellambdatreehierarchyflat
Laurent Bosc
Laurent Bosc

Reputation: 21

Flatten a hierarchy with recursive LAMBDA formula, or with Power query?

In excel I have this table with 2 columns : Table called T_DATA

dataset datasource
APP1 DTM1
DTM1 Dataset1
DTM1 Dataset2
DTM1 Dim1
DTM1 Dim2
Dataset1 Raw1
Dataset2 Dataset3
Dataset2 Raw5
Dataset3 Raw2
Dim1 Raw3
Dim1 Raw4
Dim2 Raw5
Dim2 Raw4
APP2 DTM2
DTM2 Dataset4
DTM2 Dim1
Dataset4 Raw6

I want to flat the hierarchy based on a value the user populate in cell 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[datasource];T_DATA[dataset]=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 not satisfying, or even a #N/A result :(

I give a screenshot with excel data, it will be perhaps better to understand:

enter image description here

NB: A solution in Power Query, would be a good option too, but the same. I know power query, but I don't manage to create a valid recursive solution in Power Query, too complex for me.

Upvotes: 2

Views: 77

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34355

This is a recursive function (FlatBomm2) based on a previous question :

=IF(
    COUNTIF(Range1, Parent) = 0,
    Parent,
    DROP(
        REDUCE(
            "",
            FILTER(Range2, Range1 = Parent),
            LAMBDA(a, c,
                IFNA(
                    VSTACK(
                        a,
                        IFNA(HSTACK(Parent, FlatBomm2(c, Level + 1, Range1, Range2)), Parent)
                    ),
                    ""
                )
            )
        ),
        1
    )
)

Called as

=FlatBomm2(A2,1,A2:A18,B2:B18)

Or more specific to OP's output (and level isn't needed):

=IF(
    COUNTIF(Range1, Parent) = 0,
    Parent,
    DROP(
        REDUCE(
            "",
            FILTER(Range2, Range1 = Parent),
            LAMBDA(a, c, IFNA(VSTACK(a, Parent & ">" & Flatbomm3(c, Range1, Range2)), ""))
        ),
        1
    )
)

called as

=Flatbomm3(A2,A2:A18,B2:B18)

where Flatbomm3 is defined as a lambda with arguments

Parent - the parent value (Dataset)
Range1 - the list of parents (Datasets)
Range2 - the list of children (Datasources)

or you could bundle it into a let statement as

=LET(
Flatbomm4,LAMBDA(Me,Parent,Range1,Range2,
IF(
    COUNTIF(Range1, Parent) = 0,
    Parent,
    DROP(
        REDUCE(
            "",
            FILTER(Range2, Range1 = Parent),
            LAMBDA(a,c, IFNA(VSTACK(a, Parent & ">" & Me(Me,c, Range1, Range2)), ""))
        ),
        1
    )
)),
Flatbomm4(Flatbomm4,A2,A2:A18,B2:B18))

Output

Output
APP1>DTM1>Dataset1>Raw1
APP1>DTM1>Dataset2>Dataset3>Raw2
APP1>DTM1>Dataset2>Raw5
APP1>DTM1>Dim1>Raw3
APP1>DTM1>Dim1>Raw4
APP1>DTM1>Dim1>Raw3
APP1>DTM1>Dim1>Raw4
APP2>DTM2>Dataset4>Raw6
APP2>DTM2>Dim1>Raw3
APP2>DTM2>Dim1>Raw4
Dataset2>Dataset3>Raw2
Dataset2>Raw5

Upvotes: 3

P.b
P.b

Reputation: 11628

=LET(data,A4:B20,
     F,LAMBDA(z,FILTER(">"&DROP(data,,1),TAKE(data,,1)=z,"")),
     REDUCE("Dataset2",TAKE(data,,1),
     LAMBDA(a,b,
            DROP(REDUCE("",a,
                 LAMBDA(x,y,
                        VSTACK(x,
                               y&F(TEXTAFTER(y,">",-1,,,y))))),1))))

This starts with declaring data being the two columns you have. Next we create a function F to filter column 2 if column 1 meets a certain conditions z which will be the input of the function.

Next we use a double REDUCE to add the values matching the previous result ending value

Upvotes: 3

Related Questions