Replace Values in Multiple Columns Based on Condition in Power Query

This video teaches how you can replace values based on a condition. You can for example replace all negative numbers by 0, and leave the remaining numbers untouched. In addition, this method allows you to easily replace values based on another column.

WRITTEN BLOGPOST:
The ultimate guide to replacing values in Power Query, find it here:

Master Functions and Syntax in M

Chapters:
00:00 Introduction
00:17 The dataset and goal
00:48 Replace values single column
01:06 Replace values multiple columns
01:35 Conditionally replace value on a single column
03:24 Replace values in multiple columns based on condition
04:56 Restoring data types

ABOUT BI Gorilla:
BI Gorilla shares videos and articles on Power BI and Excel to help you improve your skills.

SUPPORT MY CHANNEL

SUBSCRIBE TO MY CHANNEL

LET'S CONNECT:

Thank you for your support!

#ConditionalReplace #PowerQuery #BIGorilla

[suki]

Rick, All your tutorials, and even the specific topics you choose to focus on are all high-class Masterclasses! Thank you!

[paulgallagher]

Very clever workaround.
Also love that "Replace Value Type" trick. Thanks Rick. :D

[pravinshingadia]

I love all these techniques you show - very clever. Love your videos.

[dhyrumc]

Great Video! That worked like a charm! Thanks for taking the time to put this together.

[tetianazakharenko]

Very inspirational! Learned a lot from you

[eriknegron]

Big like from me.
Already been utilizing the conditional replace values from your previous video and have updated my current reports to remove additional steps I had.

[baruchschwartz]

Impressive ability to simplify and teach

[Kceesiu]

Thx man :) I tride to fix my problem and when a watched yours film everything was simple :)

worked a treat and saved me from creating new columns, thanks

[SharadNidaria]

Hi Rick, Thanks for your super educational videos on Power Query. I learned to conditionally replace values and put them in a custom column but I have a dataset and some condition like below.

Condition:

I have a column which *Contains* some text like "AIM", "SEO", "SELF SUPPLY" etc. and I want to create a new "Custom Column" based on this comdition and output the text as per my requirement. I want to do this within the power query.

Request you to please provide your expertise and valuable inputs. Thanks.

[nlopedebarrios]

Very useful, thanks, I subscribed ;)

[goodheart]

Hello...Thank you for your video. I have a BIG PROBLEM. I want to input A's into several columns on the same row which has number 16. How do i do that please?

[martinkeane]

Hi this is great! I have multiple columns that all should have the same rules I.e yes =1 n/a = 1.5 no =0. My problem is when following the steps I only see it on 2 conditions how do I add a third?

[ramonillarramendi]

Thank you very much. Excellent explanation and video. I'm stuck with a data set (from pdf) that has n tables n columns. I'm looking for text values that could be in any column. When using pivot, I'm getting "There were too many elements in the enumeration to complete the operation ". So ended up using the traditional Replace but instead of hardcoding Column names in replacer, took them from a previous step with Table.ColumnNames. Been trying to use Table.FindText function but haven't been able to nest it with a replace. Any ideas? Thanks a mil.

[patockadavid]

Hi Rick, I run a test explained at 2:50 and it is working for me. I used "Table.ReplaceValue(#"Changed Type", [Overall_Rating], each if [Overall_Rating] < 5 then 5 else [Overall_Rating], Replacer.ReplaceValue, {"Overall_Rating", "Service_Rating"})" and values were changed in both columns.

Small update: It works ONLY if "Overall_Rating" and "Service_Rating" are with the same value. I have no clue why... 🤫🤔🤐

[danilowalenta]

Hi Rick, thanks for your nice videos. I managed to change the value in several columns without pivoting the table by writing "Table.ReplaceValue(#"Changed Type", true, each if [Overall_Rating] < 5 then 5 else true, Replacer.ReplaceValue, {"Overall_Rating", "Food_Rating", "Service_Rating"})" . Maybe you can check if this is also working for your example :)

[williamarthur]

Can this method work if you have to transform all of the columns ?

[thembuluwodonaldnetshikulw]

Hi i have a table that has sales, and in that table I have two columns that tells you what sales is this and where it belongs, e.g I have a product columns were I have different products, then I have category that has on 3 cotagory, but now I want one category not to include one of the products, how do I update that, I can do it in the backend but I don't want to change that in the backend I want to change it in the front end

[McIlravyInc]

Is there a way to use one condition to make different replacements in the other columns?

If col A value is true, then change value in column D to yes and column E to 1 for example

[nikulpatel]

Hello,

I have one table in three columns. 1. Accepted pages 2. Non accepted pages. 3. Total pages
While accepted pages are 0 and non accepted pages are 0 then replace accepted pages 0 with total pages count.

I want to work in power query.
Can you please help me?