Adjust Multiple Columns in Power Query with another Column

This is a real world example: I had to adjust projected lifetime and annual sales figures to match the officially released figures for awarded projects. You can do this column by column, but the figures were split by customer market, in my case that meant 2 x 10 columns needed to be changed – and with additional criteria e.g. like plant location it could have been easily 40 or more. No way to do it column by column.

It is an advanced transformation and on multiple columns. So what to do?

It depends on how you approach Power Query in Excel and Power BI and also in which learning stage you are. Brian Julius mentioned recently that Gil Raviv?(PQ author and blogger DATACHANT) defines six discrete stages of learning in Power Query:

Oh, that looks as if you cannot deal with most of the data problems through the Power Query UI. And even if you can adjust the code in the formula bar more than one third of your problems you still cannot solve. That is not true!

1. Let's be honest, we deal with relatively trivial problems. We are not playing in the same league as Gil Raviv. You come a long way, if you know how to play with the UI.
2. Even if you are not very advanced you can use advanced formulas once you start with editing in the formula bar. E.g. from DATACHANT.

What is the issue? Let's see 3 alternatives to change one column:

I compared the official figures with the original lifetime sales figures to define the correction factor. That can be easily done with a custom column:

= Table.AddColumn(#"prevStep", "Factor", each 1+ ([#"Total Sales (Award)"] - [LT Sales Customer Market Total])/[LT Sales Customer Market Total], type number)

Now it would be good, if we could just transform a column by selecting in the Transform tab in the Standard dropdown Multiply and enter the column name, but you can't neither by UI nor by editing in the formula bar [Factor]. You can only multiply the column with a number:

= Table.TransformColumns(#"Added Factor", {{"EU LT Sales CM", each _ * 2, type number}}) ??

1. You can't access other columns with Table.TransformColumns, so for a full-fledged formula solution you have to use Table.TransformRows instead:

=?Table.FromRecords(Table.TransformRows(#"Added Factor", (x)=>Record.TransformFields(x, { "EU LT Sales CM", each?_ * x[Factor] } ) ))

Each row is a record i.e. a set of fields (name/value pairs).
Table.TransformRows iterates through the table rows and applies the “transform function” from the second argument to each row:
Table.TransformRows(table as table, transform as function) as list

(x)=>Record.TransformFields(x, { "EU LT Sales CM", each?_ * x[Factor] } )
You need a name for the parameter, any name is fine, e.g. row or x.

Record.TransformFields takes the row and applies "transformOperations" defined by a list of field name and “transform function” pair(s):
Record.TransformFields(record as record, transformOperations as list, optional missingField as nullable number) as record

We change the value in field “EU LT Sales CM” by multiplying with the value from field [Factor] of the same row, thus one list with one pair is enough.

For multiple fields “transformOperations list” is a list of lists with pairs:
{ {FieldName1, transform as function}, {FieldName2, transform as function} … }        

2. More convenient would be a hack from the editing in the formula bar category. Select the column you want to transform and make a right mouse selection of Replace Values. Type null as place holders and then replace the nulls by the column's values and the column's values times the factor:

= Table.ReplaceValue(#"Added Factor", each [EU LT Sales CM], each [EU LT Sales CM] * [Factor], Replacer.ReplaceValue,{"EU LT Sales CM"})

3. Third possibility would be to add either a custom column or selecting in the Add Column tab in the Standard dropdown Multiply and then entering the column name:

The result could look like this. You would have to delete the original column and then rename the added column to get the same result as with the two other options, no big deal.

= Table.AddColumn(#"Added Factor", "EU LT Sales CM2", each [EU LT Sales CM] * [Factor], type number)

All three solutions render the same result, so it doesn't give you any advantage if you can write the formula of the first solution. I would use the second solution as it is super easy to apply (everyone knows how to replace values) and it avoids the deletion and renaming steps of the third solution.

And how to transform multiple/many columns at once?

1. Above we have seen that the formula solution can be expanded to multiple columns:

=?Table.FromRecords(Table.TransformRows(#"Added Factor", (x)=>Record.TransformFields(x, {{ "EU LT Sales CM", each?_ * x[Factor] }, { "CN LT Sales CM", each?_ * x[Factor] }, { "JP LT Sales CM", each?_ * x[Factor] }} ) ))

It is always the same transform function and thus you could re-write these pairs:

=?Table.FromRecords(Table.TransformRows(#"Added Factor", (x)=>Record.TransformFields(x, List.Transform( { "EU LT Sales CM", "CN LT Sales CM", "JP LT Sales CM" }, (y) => { y, each _ * x[Correction] } ) ) ))

List.Transform applies the “transform function” from the second argument to each value of the list:
List.Transform(list as list, transform as function) as list

(y) => { y, each _ * x[Correction] }
You need a name for the parameter, any name is fine, e.g. val or y.

Each field name from the list is transformed into a list with a pair of FieldName and "transform function" resulting into a list of lists like this:
{ {FieldName1, transform as function}, {FieldName2, transform as function} … })        

That's great, but you wouldn't do this for two or three columns. ??

But you would do it for ALL relevant columns - in my case all columns with lifetime or average sales for different customer markets. Here is how you select the relevant columns :

= Table.FromRecords(Table.TransformRows(#"Inserted Factor", (x)=>Record.TransformFields(x, List.Transform( List.Select(Table.ColumnNames( #"Added Factor"), each Text.Contains(_,"Sales CM")), (y) => { y, each _ * x[Correction] } ) ) ))

That is a totally dynamic solution which could also be easily expanded for additional columns e.g. containing "Sales Plant Loc". I love it!

2. Here I wanted to include a solution with List.Accumulate. Actually I started this article only because I thought it was a good chance to write about a List.Accumulate application. Instead I just leave two List.Accumulate variants in the comments and go for a UI plus lightweight editing solution. Less prestigious, but simple is often better.

If you have to deal with many columns and potentially changing column names unpivoting is a good candidate, to create a dynamic solution.

Simply mark any column and select in the Transform tab in the Unpivot Columns dropdown Unpivot Only Selected Columns:

= Table.Unpivot(#"Added Factor", {"EU LT Sales CM"}, "Attribute", "Value")

Replace the curly bracket stuff with the dynamic column selection from above:

= Table.Unpivot(#"Added Factor", List.Select(Table.ColumnNames( #"Added Factor"), each Text.Contains( _,"Sales CM")), "Attribute", "Value")

Select [Value] and make a right mouse selection of Replace Values. Type null as place holders and then replace the nulls by the Value values and the Value values times the factor:

= Table.ReplaceValue(#"Unpivoted Only Selected Columns", each [Value], each [Value] * [Factor], Replacer.ReplaceValue,{"Value"})

Now you can pivot back, select [Attribute] and in the Transform tab click on Pivot Column. As the Values Column choose Value:

= Table.Pivot(#"Unpivoted Only Selected Columns", List.Distinct(#"Unpivoted Only Selected Columns"[Attribute]), "Attribute", "Value", List.Sum)

Be aware that you do lose any empty columns in this process.

3. Third, if your columns won't change you can stick completely to the UI.

Mark all relevant columns and select in the Transform tab in the Unpivot Columns dropdown Unpivot Only Selected Columns.

Then add either a custom column or select in the Add Column tab in the Standard dropdown Multiply and enter the column name Factor as above.

The result could look like this. You would then have to delete the original Value column:

= Table.AddColumn(#"Added Factor", "Value2", each [Value] * [Factor], type number)

Now you can pivot back, select [Attribute] and in the Transform tab click on Pivot Column. As the Values Column choose Value2.

All three solutions render the same result, but the third one is static and has an additional step. So I would opt for either one or two: The first one is nice and short, the second one takes more clicks, but less thinking and knowledge.

And here is a blackbelt solution!

The above was about showing that you can solve an apparently difficult problem entirely by UI or with UI plus lightweight editing in the formular bar. White belt and yellow belt are ok! Also the function combination is not what you would call free-form coding in M.

This blackbelt solution from Aleksei Zhigulin needs a lot of knowledge to create, but it is awestriking simple, and once understood it is like Columbus' egg: You can do it too!

You start with the UI and then do some lightweight editing in the formular bar. Select any column and make a right mouse selection of Replace Values. Type null as place holders:

= Table.ReplaceValue(#"Added Factor", null, null, Replacer.ReplaceValue,{"AnyCol"})

Replace the curly bracket stuff with the dynamic column selection from above:

= Table.ReplaceValue(#"Added Factor", null, null, Replacer.ReplaceValue, List.Select( Table.ColumnNames(#"Added Factor"), each Text.Contains(_,"Sales CM")) )

Replace the second null and the Replacer.ReplaceValue and you are done!

= Table.ReplaceValue(#"Added Factor", null, each [Factor], (a,b,c) => a*c, List.Select( Table.ColumnNames( #"Added Factor"), each Text.Contains(_,"Sales CM")) )

Table.ReplaceValue replaces in all columnsToSearch the oldValue with a newValue according to a replacer function:
Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list) as table

The replacer functions Replacer.ReplaceValue or Replacer.ReplaceText have 3 inputs.
You can define a custom replacer function using 2 of these inputs.

(a,b,c) => a*c
You need a name for the parameters, any name is fine, e.g. col,old,new or a,b,c.        

Simply adapt the function to divide, add, substract, get Min, Max, Average, ...

Questions and suggestions:?https://www.dhirubhai.net/in/matthiasfriedmann

?Let?me?help?you?with?your?data!

"LIKE", "COMMENT" or "REPOST" this article,?to give your network a chance to find it!