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