This article demonstrate how to replace multiple columns value at once in Power Query. Lets say, you need to check if column value is blank then replace it with 0. For this you go to column and replace null values with zero using power query replace blank values feature and for that a step is created in Power Query.
In case, if you have to do same thing for multiple columns then you have to repeat same steps for those column as well.
So, rather than going each column and repeat same step again and again, you can replace multiple column values at once.
As you can, see here we have employee sales table. Now the requirement is to replace null values with zero for column Qty, Amount Per Qty and Discount.
Lets replace blank values in QTY column first after that we will see how to replace multiple columns values at once.
Go to Power Query Editor, after that right click on QTY column header and select replace values from context menu as shown below.
Once you click on Replace Values.., a pop up window opens, here you need to provide a new value that you want replace with old values.
In our case we want to replace null values with zero so we provide values accordingly as shown below.
Once you done with this, you can see it quickly replace null values with zero.
Also, creates a Replace Value step in Applied Steps.
Now we will modify Replace Value step to replace multiple column values as shown below.
Just add columns name by separating comma just right after Qty column.
= Table.ReplaceValue(#"Changed Type",null,0,Replacer.ReplaceValue,{"Qty", "Amount Per Qty", "Discount"})
Now you can see, we have modified the Replace Value step to replace null values in Amount Per Qty and Discount column as well.
Also Read..
Replace multiple values in one column Power Query
