Forum Discussion
Format loss when unpivoting columns
If the unpivoted columns do not share the same data type it will revert to any, or ABC/123. You just need to change the type after the unpivot operation, then change or confirm the format in Power BI.
Note that data type and format are not the same. You can have it a a currency data type, but still format it as a percent, whole number, etc. in the report layer. But it is important to set the data type in Power Query.
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
