Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
sean_cochran
Helper II
Helper II

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 DAX). List.Generate seems like it could fit my needs.

 

I tried to create the column using List.Generate as below:

sean_cochran_1-1689975377442.png

 

 

 

let 

table = Table.Buffer(#"Grouped Rows")

in

List.Generate(
    () => [emp = [employeeID], super = [supervisorID]],
    each [super] <> null,
    each [emp = [super], super = (a)=> List.Max(Table.SelectRows(table, (b)=> [employeeID] = (a)[super])[supervisorID])],
    each [emp]
)

 

 

 

As I understand it, the code I wrote is supposed to do the following:

 

  1. Define two variables and their starting points (emp as the current row's employee, super as the parent employee)
  2. Check whether the current state of [super] is null. If so, then end loop. If not, then proceed.
  3. Replace the value of [emp] with the value of [super], and set the value of [super] to the current [super's] parent employee.
  4. Add the current value of [emp] to the list.
  5. Repeat until [super] is null.

 

This works fine until the function is supposed to take over, at which point the list just says "function" for every record (see screenshot). Is there something I'm missing? Does List.Generate simply not allow functions in this area?

 

Thanks in advance! 

1 ACCEPTED SOLUTION
AlienSx
Super User
Super User

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.

 

View solution in original post

5 REPLIES 5
sean_cochran
Helper II
Helper II

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"
sean_cochran
Helper II
Helper II

This seems to work perfectly, but I can't claim to fully understand it. I'll be studying this for a while! Thank you for your help.

AlienSx
Super User
Super User

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.

 

Can you clarify a few things for me?

 

I'm used to seeing [next] used to reference table columns that have already been defined. In your solution, I can't see where the column [next] has been defined, but you refer to it in the function that creates the 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],

 

 

Maybe I'm misunderstanding something. I'm still trying to parse what you did here.

 

Thank you again for your help!

Nevermind! I think I got it. You told the function to expected a record "r" that contains the field "next", which is itself a varible, and you feed employee records into it via the list.generate statement. Still not sure I understand all the details, but I think I have the keys to figure it out now.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors