Forum Discussion

palmeirense_'s avatar
palmeirense_
Frequent Visitor
4 years ago

Format loss when unpivoting columns

I am working on a report where in the source file, I have a set of columns expressed in different format types (a few are percentages, some are currency, others are fixed decimal number).

Because my end users should be able to slice my visuals by the values in a single column, I had to unpivot columns, however, when doing that, I lose all that formatting. How can I get around this issue?

Thanks!

Need Help

8 Replies

  • edhans's avatar
    edhans
    Community Champion
    4 years ago

    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.

    • palmeirense_'s avatar
      palmeirense_
      Frequent Visitor
      4 years ago

      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?

      • edhans's avatar
        edhans
        Community Champion
        4 years ago

        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.

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    Hi, palmeirense_ 

    You can use Switch function and format function to help you
    When you unpivot the columns, you get an attribute column.
    Create a measure like below

    _Switch = 
SWITCH(
    SELECTEDVALUE('Table'[Attribute]),
    "Percentage",FORMAT(SELECTEDVALUE('Table'[Value]),"Percent"),//columnName1
    "Currency", FORMAT(SELECTEDVALUE('Table'[Value]),"Currency"),//columnName2
    "Decimal number", FORMAT(SELECTEDVALUE('Table'[Value]),"Fixed"))//columnName3

    Result:

    Please refer to the attachment below for details.

    Hope this helps.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.