Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a custom function which undertakes some ETL and then adds a column which is a List.Sum or Text.Combine of the values in some other columns. It works fine but I have to do this 60+ times to build the final table. Again it works okay but it's pretty slow and inelegant. I was wondering if there is a way to call this function n times (i.e. 60+ times) passing 2 parameter values from a table (or perhaps 2 lists)? Alternatively is there some other approach or methodology that could make this run quicker & simpler?
The calling function is as follows. I can put the 2nd and 3rd parameter into a table or a pair of lists, so I imagine there might be a way to iterate over the table or list and run the function for each row or item.
let Set1 = fxAggregatedSet(TableName as table, "TimeSpentAwayFromOffice" as text, "TimeSpentAwayFromOffice" as text, ColumnsToUpdate as table, UniqueSetNames as table) as table, Set1a = fxAggregatedSet(Set1 as table, "TimeSpentInOffice" as text, "TimeSpentInOffice" as text, ColumnsToUpdate as table, UniqueSetNames as table) as table, Set1b = fxAggregatedSet(Set1a as table, "TimeSpentOther" as text, "TimeSpentOther" as text, ColumnsToUpdate as table, UniqueSetNames as table) as table etc etc 60+ times
The custom function which adds the column is:
let fxAggregatedSet = (TableName, SetName, SetNameRoot, ColumnsToUpdate, UniqueSetNames) => let // get list of columns to be added together from main lookup table SetNameLength = Text.Length(SetNameRoot), Set_List = Table.ToList(Table.SelectRows(Table.SelectColumns(ColumnsToUpdate, "NewColumnNames"), each Text.Start([NewColumnNames], SetNameLength) = SetNameRoot )), // Get ZeroColumn value as text Step1 = Table.SelectRows(UniqueSetNames, each [RootNames] = SetName ), Step2 = Table.SelectColumns(Step1, "ZeroColumns"), Step2a = Table.AddColumn(Step2, "TrueFalseText", each if [ZeroColumns] = true then "True" else "False"), Step2b = Table.SelectColumns(Step2a, "TrueFalseText"), Step3 = Table.ToList(Step2b), AddAggregatedColumn = if not List.Contains(Table.ToList(Table.SelectColumns(UniqueSetNames,"RootNames")), SetName) then /* if there's no column to update */ if List.Contains(Step3, "True") then /* if it's a zero column */ Table.AddColumn(TableName, SetName, each 0) else Table.AddColumn(TableName, SetName, each null) else if List.Contains(Step3, "True") then /* if it's a zero column */ Table.AddColumn(TableName, SetName, each /* use List.Sum to add percentage values e.g. 0.1, 0.8 */ List.Sum( Record.FieldValues( Record.SelectFields(_, Set_List) ) ) , Percentage.Type ) else Table.AddColumn(TableName, SetName, each /* use Trim and Combine to combine strings (actually it is lots of blanks and a string) */ Text.Trim( Text.Combine( Record.FieldValues( Record.SelectFields(_, Set_List) ) ) ) , type text ) in AddAggregatedColumn in fxAggregatedSet
Solved! Go to Solution.
I use List.Accumulate to Add Columns to tables frequently. For example, I could use the code at the bottom to add calculated columns where Calc2 = Column1+Column2 and Calc3 = Column2+Column3, etc.
Regards,
Mike
Table1
letter | Column1 | Column2 | Column3 | Column4 |
a | 1 | 2 | 3 | 4 |
b | 4 | 5 | 6 | 7 |
Table2
letter | Column1 | Column2 | Column3 | Column4 | Calc2 | Calc3 | Calc4 |
a | 1 | 2 | 3 | 4 | 3 | 5 | 7 |
b | 4 | 5 | 6 | 7 | 9 | 11 | 13 |
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], CalcColumns = {1..List.Count(List.Select(Table.ColumnNames(Source), each Text.StartsWith(_,"Column")))-1}, AddCalcColumn = List.Accumulate(CalcColumns, Source, (s,c) => Table.AddColumn(s, "Calc"&Text.From(c+1), each List.Sum( Record.ToList(Record.SelectFields(_, {"Column" & Text.From(c), "Column" & Text.From(c+1)}) )))) in AddCalcColumn
I use List.Accumulate to Add Columns to tables frequently. For example, I could use the code at the bottom to add calculated columns where Calc2 = Column1+Column2 and Calc3 = Column2+Column3, etc.
Regards,
Mike
Table1
letter | Column1 | Column2 | Column3 | Column4 |
a | 1 | 2 | 3 | 4 |
b | 4 | 5 | 6 | 7 |
Table2
letter | Column1 | Column2 | Column3 | Column4 | Calc2 | Calc3 | Calc4 |
a | 1 | 2 | 3 | 4 | 3 | 5 | 7 |
b | 4 | 5 | 6 | 7 | 9 | 11 | 13 |
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], CalcColumns = {1..List.Count(List.Select(Table.ColumnNames(Source), each Text.StartsWith(_,"Column")))-1}, AddCalcColumn = List.Accumulate(CalcColumns, Source, (s,c) => Table.AddColumn(s, "Calc"&Text.From(c+1), each List.Sum( Record.ToList(Record.SelectFields(_, {"Column" & Text.From(c), "Column" & Text.From(c+1)}) )))) in AddCalcColumn