Skip to main content Skip to footer

Power Query for Excel – Replace values using values from another column

Another video trick for Power Query. A glimpse of how advanced you can go with Power Query using custom M code.

Be sure to check out more tips and tricks like this one on the oficial Power Query Training page

Categories:

11 Comments

Frank

Nice approach! This is an alternative one:

let
Source = Excel.CurrentWorkbook(){[Name=”Tabelle1″]}[Content],
ReplaceNullsWithZeros = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,{“Product”, “Jan”, “Feb”, “Mar”, “Apr”, “May”, “Jun”, “PastSemesterAverage”}),
UnpivotColumns = Table.UnpivotOtherColumns(ReplaceNullsWithZeros, {“Product”, “PastSemesterAverage”}, “Attribut”, “Wert”),
AddColumn1 = Table.AddColumn(UnpivotColumns, “Type”, each if [Wert]=0 then “FORECAST” else “FACT”),
AddColumn2 = Table.AddColumn(AddColumn1, “Value”, each if [Wert] = 0 then [PastSemesterAverage] else [Wert]),
RemoveColumns = Table.RemoveColumns(AddColumn2,{“PastSemesterAverage”, “Wert”})
in
RemoveColumns

Miguel Escobar

Hey!

That works too! Always happy to see more people using Power Query 🙂

Thank you for sharing!

Ivan

Awesome…

Ben Dymit

When I used this technique, it changed all of my data types to “any”. Is there a fix to that, or something I just need to be aware of?

Thanks!

Miguel Angel Escobar

hey Ben!

Thanks for pointing that out. I’m trying to get in touch with the Microsoft Product Team to see if this is a bug or a by design result.
I’ll get back to you once I have more info.

Ben Dymit

Great–thank you! This method is much cleaner than creating a conditional column with an If statement, I think, so I would like to use it more widely.

Thank you again.

Miguel Angel Escobar

Ben,

Sorry for the late reply. I got a response from the Microsoft team a few days ago and it appears that this behavior is by-design, but thank you for bringing it up! I wasn’t aware of it, but one of the suggestions when working with Power Query is to always create a last step defining your data types especially for cases like this one where the columns go into an any state.

Ben Dymit

Thank you for following up! I suppose that makes sense to define data types as a last step–I always did it as my first step, but now that I think of it I then change data types on columns that I delete later.

Anyway, thanks for the help!

Miguel Angel Escobar

Hey Ben,

Here’s a quick workaround to that scenario in the event that you don’t wanna do the whole ‘manually defining the data type of each column’ all over again:
https://youtu.be/UZQ9EFvEECY

Ben Dymit

Awesome! Thank you!

Oswaldo

Awesome, glad you figure it out!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.