Forum Discussion
List.Generate to recreate PATH dax command in power query m
- 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 outproviding 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.