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

Posted in:

10
Leave a Reply

avatar
3 Comment threads
7 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
5 Comment authors
Ben DymitMiguel Angel EscobarIvanMiguel EscobarFrank Recent comment authors
  Subscribe  
newest oldest most voted
Notify of
Frank
Guest
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

Ivan
Guest
Ivan

Awesome…

Ben Dymit
Guest
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
Guest

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
Guest
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
Guest

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
Guest
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
Guest

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
Guest
Ben Dymit

Awesome! Thank you!