Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am in search of m query that adds a column to display all Children at level 0 in a table with Parent/Child relation. I am already using a function but its painfully slow.
****fnParChAllChildren**** let Source = (ParChTable as table, ChildKey as text, ParentKey as text) as table => let AllChildren = Table.AddColumn(ParChTable, "AllChildren", each List.Skip( List.Generate(()=> [Children={Record.Field(_, ChildKey)}], // (List of) records as start values each List.Count([Children]) > 0, // condition under which the next loop is execute each [ Children= List.Buffer(fnRecColumnAsList( Table.SelectRows(ParChTable, (ParChTable) => List.Contains([Children], Record.Field(ParChTable, ParentKey))) , ChildKey))], // Executable action each [Children]) // (Selection of) return ,1)), // Skip first element in list which is it's own key LstAllChildren = Table.AddColumn(AllChildren, "LstAllChildren", each List.Combine([AllChildren])), Level = Table.Buffer(Table.AddColumn(LstAllChildren, "Level", each List.Count([AllChildren]))), Indent = Table.AddColumn(Level, "Indent", each List.Max(Level[Level])*4-[Level]*4), JustLevel0 = List.Buffer(fnRecColumnAsList(Table.SelectRows(Indent, each [Level]=0), ChildKey)), AllChildrenJustLevel0 = Table.AddColumn(Indent, "ChildrenJustLevel0", each List.Intersect({[LstAllChildren], JustLevel0})) in AllChildrenJustLevel0 in Source
****fnRecColumnAsList***** let Source = (Table as table, ColumnName as text) as list => Table.ToColumns(Table.SelectColumns(Table, ColumnName)){0} in Source
I forgot where I got it from but it works and I use column AllChildrenJustLevel0 generated by it. Only bad thing is its painfully slow.
Solved! Go to Solution.
I got it to work faster by putting Table.Buffer step just before calling it.
I got it to work faster by putting Table.Buffer step just before calling it.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.