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 Comments

  1. 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

  2. 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!

      • 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.

        • 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.

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.