Forum Discussion

sean_cochran's avatar
sean_cochran
Icon for Resolver I rankResolver I
3 years ago
Solved

List.Generate to recreate PATH dax command in power query m

I have a table that lists employees and their parent employees.   I want to add a column that contains a concatenated list of all parent employees in the hierarchy (similar to the PATH function in ...
Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    Hello, sean_cochran List.Generate generates list of smth - scalars, records, lists, maybe list of functions or list of whatever you want it to generate. In your particular example a list of (a) => ... functions is generated. Calculate something. While you are working on your own solution may I suggest to consider mine? This was entertaining. The idea is to grab bosses from bottom to top levels first, then go over this list again to add bosses to each employee step by step. 

     

    let
    Source = your_table,
    // make an initial record ee as fields and and bosses as values
    rec = Record.FromList(Source[supervisorID], Source[employeeID]),
    // this function creates next level of supervisors
    fx = (r as record) =>
        [d = List.Difference(Record.FieldNames(r[next]), Record.FieldValues(r[next])),
        out = [lst = Record.SelectFields(r[next], d), next = Record.RemoveFields(r[next], d), flag = Record.FieldCount(r[next]) > 0]][out],
    // this List.Generate goes from bottom level to top and creates lists of supervisors
    gena = 
        List.Generate(
            () => fx([next = rec]),
            (x) => x[flag],
            (x) => fx(x),
            (x) => x[lst]
        ),
    p = {1..(List.Count(gena) - 1)},
    // here we go from bottom to top and add supervisors step by step
    acc = 
        List.Accumulate(
            p,
            Table.FromColumns({Record.FieldNames(gena{0}), List.Transform(Record.FieldValues(gena{0}), each {_})}, {"employee", "super"}),
            (s, c) =>                
                    [g = gena{c},
                    employees = Record.FieldNames(g),
                    supervisors = Record.FieldValues(g),
                    tbl = Table.FromColumns({employees, List.Transform(supervisors, each {_})}, {"employee", "super"}),
                    add_super = Table.TransformColumns(s, {"super", (x) => x & Record.FieldValues(Record.SelectFields(g, x, MissingField.Ignore))}),
                    add_level = Table.Combine({add_super, tbl})][add_level]
        ),
    out = Table.TransformColumns(acc, {"super", (w) => Text.Combine(w, ", ")})
in
    out

    providing that you have 1 supervisor per each employee.

     

sean_cochran's avatar
sean_cochran
Icon for Resolver I rankResolver I
3 years ago

I've applied the solution from AlienSx , but when used in a dataflow, it seems to cause issues with nightly scheduled dataflow refreshes: "Error: Expression.Error: We expected a FieldsSelector value. <ccon>{null}</ccon>. RootActivityId = c9335db1-3174-400f-ae83-3286d034d25c.Param1 = Expression.Error: We expected a FieldsSelector value. <ccon>{null}</ccon> Request ID: d871921e-4364-488b-9744-036185561172."

 

I can repost this elsewhere if that's required, but since the issue is specific to this solution, I wanted to list it here at least. Does anyone have any ideas? Here is the query code:

 

let
    Source = #"Employment Details",
    #"Filtered Rows" = Table.SelectRows(Source, each [employeeStatusCode] = "A" and [companyID] <> "A76BG"),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"employeeID"}, {{"supervisorID", each List.Max([supervisorID]), type nullable text}}),
    employees = Table.Buffer(#"Grouped Rows"),
    // make an initial record ee as fields and and bosses as values
    rec = Record.FromList(employees[supervisorID], employees[employeeID]),
    // this function creates next level of supervisors
    fx = (r as record) =>
    [d = List.Difference(Record.FieldNames(r[next]), Record.FieldValues(r[next])),
    out = [lst = Record.SelectFields(r[next], d), next = Record.RemoveFields(r[next], d), flag = Record.FieldCount(r[next]) > 0]][out],
    // this List.Generate goes from bottom level to top and creates lists of supervisors
    gena = List.Generate(
    () => fx([next = rec]),
    (x) => x[flag],
    (x) => fx(x),
    (x) => x[lst]
    ),
    p = {1..(List.Count(gena) - 1)},
    // here we go from bottom to top and add supervisors step by step
    acc = List.Accumulate(
    p,
    Table.FromColumns({Record.FieldNames(gena{0}), List.Transform(Record.FieldValues(gena{0}), each {_})}, {"employee", "super"}),
    (s, c) =>
    [g = gena{c},
    employees = Record.FieldNames(g),
    supervisors = Record.FieldValues(g),
    tbl = Table.FromColumns({employees, List.Transform(supervisors, each {_})}, {"employee", "super"}),
    add_super = Table.TransformColumns(s, {"super", (x) => x & Record.FieldValues(Record.SelectFields(g, x, MissingField.Ignore))}),
    add_level = Table.Combine({add_super, tbl})][add_level]
    ),
    #"Expanded super" = Table.ExpandListColumn(acc, "super"),
    #"Filtered rows 1" = Table.SelectRows(#"Expanded super", each [super] <> ""),
    #"Transform columns" = Table.TransformColumnTypes(#"Filtered rows 1", {{"super", type text}}),
    #"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"super", null}})
in
    #"Replace errors"