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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Power Query adding n columns iteratively calling a custom function each time

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

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

letterColumn1Column2Column3Column4
a1234
b4567

 

Table2

letterColumn1Column2Column3Column4Calc2Calc3Calc4
a1234357
b456791113

 

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

 

View solution in original post

1 REPLY 1
Anonymous
Not applicable

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

letterColumn1Column2Column3Column4
a1234
b4567

 

Table2

letterColumn1Column2Column3Column4Calc2Calc3Calc4
a1234357
b456791113

 

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors