Forum Discussion
Format loss when unpivoting columns
Apologies as I might not have explained this very well.
In my first power query step, I have 5 columns which get unpivoted into only 1 column. I can change formats for this resulting column but I want to be able to have different data types depending on the row value. Is that possible?
No. Each column can only have one data type, or it can be a variant, where it is whatever, but that will always look like text to Power BI and is a mess.
Don't combine data types into a single column. It might be helpful to explain what you are doing, but putting units, dollars, percents, and text into a single column via Unpivot in Power BI is very likely not the right way to go.
In my report, I have a line chart showing trended metrics on a monthly basis. My hope was, the user could pick which metric they want to trend out from a slicer and if it's dollars per unit, it would show up as currency, if it's share, it would show as a percentage, etc. Maybe I'll explore a different way of showing that
Use a SWITCH measure. See Write Conditional Statement Using SWITCH in DAX and Power BI - RADACAD for guidance.
You could also use a Calculation Group, but that is next level. A SWITCH measure is the cleanest and quickest to implement. Only explore CG's if the performance is not acceptable.I tried SWITCH but is there a way to maintain different formatting options within that expression? I couldn't figure it out