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

Power Query
Notify of
11 Comentario
Newest Most Voted
Inline Feedbacks
View all comments

Nice approach! This is an alternative one:

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”})



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?


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


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:

Ben Dymit

Awesome! Thank you!


Awesome, glad you figure it out!