Power Query – Replace values for whole table

Last modified date

Today’s challenge was to replace the values on all the columns in the query when I know the columns will change so I don’t want to name them.

Quick Answer

For those who don’t want the long explanation. Do replace values on at least one column to get the replace step. In the example below the previous step is #’Changed Type’ and the columns are Jan 20 and Feb 20.

= Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Jan 20", "Feb 20"})

Remove the {…} section and replace it with Table.ColumnNames(Previous Step)

= Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,Table.ColumnNames(#"Changed Type"))
Replace values with named columns
Replace values on all columns

Longer Answer

Replace values is a powerful tool for coping with null values and errors. When you select multiple columns and select Replaces values a step is written in that includes a list of column names, eg {“column1″,”column2″,”column3”}. If your data structure changes that step will either break because a column is missing or will not include a new column.

The function Table.ColumnNames(StepName) returns a list of column names from the named step. To see this result and filter the list right click on a step and select Insert Step After. Edit the new step to be Table.ColumnNames(StepName)

Column name list

The list of column names can be filtered, even though there is no drop down arrow. Right click on the value to remove and select Remove Item.

column name list

If I then rename this step to ColumnNames I can then use that step name in a replace step.

Replace Values using named step

Note

Yes the Replaced Value step is refering to a calculation that comes after it (yup its wacky). If you reorder the steps by dragging them Power Query will decide to change the code so steps refer to the new previous step.

References

Microsoft Docs Table.ColumnNames

Over 20 year experience at being passionate about training, solving problems and loving a new challenge especially in the Microsoft's Power Platform suite.