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 for Excel – Replace values using values from another column
THE AUTHOR
Miguel Escobar
Excel specialist turned into BI specialist using the latest tools from Microsoft for BI – Power BI. He is the co-author of ‘M is for Data Monkey’, blogger and also Youtuber of powerful Excel video Tricks. He has been recognized as a Microsoft Most Valuable Professional (MVP), is a Microsoft Certified Professional (MCP – MCSA: BI Reporting), a Microsoft Certified Trainer (MCT), and is one of the international pioneers in Power Pivot, Power Query and Power BI.
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
Hey!
That works too! Always happy to see more people using Power Query 🙂
Thank you for sharing!
Awesome…
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!
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.
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.
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!
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
Awesome! Thank you!
Awesome, glad you figure it out!