Power Query Tutorial: How to Conditionally Replace Values Without Creating New Columns

In this tutorial, I'll show you how to use conditional replacement in Power Query to transform and clean up your data. Conditional replacement is a powerful feature in Power Query that allows you to selectively replace values in a column based on a condition in another column.

We'll start by introducing you to the concept of conditional replacement and how it can help you manipulate and transform data in a variety of scenarios. We'll then dive into the practical steps of using the feature in Power Query, including creating a custom column, defining a condition, and replacing values based on that condition.

We'll provide examples and walk you through each step of the process so that you can follow along and try it yourself. By the end of this tutorial, you'll have a solid understanding of how to use conditional replacement in Power Query, and how it can help you clean and transform your data quickly and efficiently.

0:00 Intro and Problem
1:38 Custom Column Solution
3:37 Conditional Replace Solution
4:09 Fixing the Code

Microsoft Excel Tutorial - how to replace values without using custom columns? How to conditionally replace values in one step?

Using the formula:
= Table.ReplaceValue(#"Renamed Columns","X",
each if [ColumnA] = "X" and [ColumnB] = "Y" then "A"
else if [ColumnA] = "X" and [ColumnB] = "Z" then "B"
else [ColumnA],Replacer.ReplaceText,{"ColumnA"})

ColumnA = your replacement column
ColumnB = your reference conditional column
X is what you're replacing
Y = first conditional
A = what you want to change X to based on Y
Z = second conditional
B = what you want to change X to based on Z

#powerquery #powerquerytutorials #exceltutorial

[allabout]

What the crack. I can not believe what I saw No way... Just no way. Tomorrow I will test this. Man... Thank You. Waiting for more videos.

[donniemcgee]

Thank you so much for sharing this. Very helpful!

[zzota]

That's really cool, thank you. Subscribed 😁

[RadioFreeYichen]

This is the simplest tutorial I’ve found on YouTube. Thanks!

[jerrymigliaccio]

Clever use of the GUI to help write the M-code for find and replace! This is very useful.

[mirrrvelll]

I am shocked by this. Didnt even know you can write there in UI Replace...wow! got any new ideas/videos on this? can it be used in another UIs and so on?

[josevaldesv]

A new world of opportunities just opened up!