Miguel Escobar Published May 8, 2015

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

Power Query

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

Did you find this article useful?

Power Query
0 0 votes
Article Rating
Subscribe
Notify of
guest
11 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Frank
Frank
6 years ago

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

Ivan
Ivan
6 years ago

Awesome…

Ben Dymit
Ben Dymit
4 years ago

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
Reply to  Ben Dymit
4 years ago

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
Ben Dymit
Reply to  Miguel Angel Escobar
4 years ago

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
Reply to  Ben Dymit
4 years ago

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
Ben Dymit
Reply to  Miguel Angel Escobar
4 years ago

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
Reply to  Ben Dymit
4 years ago

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
Ben Dymit
Reply to  Miguel Angel Escobar
4 years ago

Awesome! Thank you!

Oswaldo
Oswaldo
1 year ago

Awesome, glad you figure it out!