[TIP] Functie Recursiva in Power Query

[Catalin B.]

Entuziasmul Dr.Excel e molipsitor , asa ca mai vin si eu cu o noutate, o metoda de prelucrare date recursiva de aceasta data.
Acest lucru se face destul de usor in visual basic cu un User Defined Function, dar... e cel putin la fel de usor si in Power Query.
Tabelul de date initial:

Cod: Selectaţi tot

Superior Organization	Organization
Group A	Group B
Group B	Group C
Group C	Group D
Group D	Group E

Problema: Identificarea tuturor relatiilor dintre organizatii.

Evident, incepem cu incarcarea tabelului initial in Power Query, apoi adaugam o coloana noua si apela functia recursiva, care va cauta toate nivelele inferioare pentru organizatia superioara:

Cod: Selectaţi tot

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each  {[Superior Organization]} & GetOrg(Source,[Superior Organization])),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(_, ";"), type text})
in
    #"Extracted Values"

Functia recursiva initiata de interogarea de mai sus se va auto-apela pana cand nu mai exista nici un nivel inferior:

Cod: Selectaţi tot

(Tbl,Org)=>
let
    Source = Tbl,
    Filter=Table.SelectRows(Source, each _[Superior Organization] = Org),
    OrgName=Filter[Organization]{0},
    Result=try {OrgName} & GetOrg(Tbl,OrgName) otherwise {}
in
    Result

Mai ramane de facut o simpla separare in coloane a rezultatului obtinut.
In interogarea initiala, am preferat sa afisez rezultatul combinat intr-o singura caseta, prin aplicarea functiei Text.Combine la lista produsa de functia recursiva.
Se poate face si altfel, in multe feluri: putem de exemplu sa configuram functia sa produca un rezultat de tip tabel, in loc de lista si sa expandam tabelele in coloane.
Sau, in loc de Text.Combine, transformam lista produsa de functia recursiva in tabel:

Cod: Selectaţi tot

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each  {[Superior Organization]} & GetOrg(Source,[Superior Organization])),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Table.Transpose(Table.FromList(_)), type table }),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Extracted Values", "Custom", Table.ColumnNames(Table.Combine(#"Extracted Values"[Custom])), Table.ColumnNames(Table.Combine(#"Extracted Values"[Custom])))
in
    #"Expanded Custom"

Nota: functia cauta doar nivelele inferioare, se poate construi in mod similar o functie care sa extraga nivelele superioare, dupa care se pot combina cele 2 liste pentru un rezultat complet.