Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

format data after transpose table

Hi,

I got the data from source in this format:

 

I have applied transpose table on this and now data is coming as per the expectation :

 

I am experiencing trouble to format the individual row's values. Avg Sale value should be in % format, Total sales should have $ before the value, Pending sshould be decimal but all this not possible because these are not column but rows. 

I have tried to set formating in power query 1 step before the transpose table but it got vanished after transpose table step.

 

Please suggest how to set the formatting for as required above.

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  Anonymous ,

    It is not possible to display multiple different formats on the same column. You can use the format() function to convert the table to the form you want after Unpivoting the table, but both are text types.

    Here are the steps you can follow：

    1. In the power query, except [Group], the rest are selected – Unpivot Columns.

    2. Create measure.

    Measure =
SWITCH(
    TRUE(),
    MAX('Table2'[Attribute])= "Total sales",FORMAT( MAX('Table2'[Value]),"$#,##0"),
    MAX('Table2'[Attribute])= "Pending",FORMAT(MAX('Table2'[Value]),"Fixed"),
    MAX('Table2'[Attribute])="Avg Sale",FORMAT(MAX('Table2'[Value]),"Percent"),
    FORMAT(MAX('Table2'[Value]),"General Number")
    )

    3. Result:

     

    Best Regards,

    Liu Yang

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  Anonymous ,

    It is not possible to display multiple different formats on the same column. You can use the format() function to convert the table to the form you want after Unpivoting the table, but both are text types.

    Here are the steps you can follow：

    1. In the power query, except [Group], the rest are selected – Unpivot Columns.

    2. Create measure.

    Measure =
SWITCH(
    TRUE(),
    MAX('Table2'[Attribute])= "Total sales",FORMAT( MAX('Table2'[Value]),"$#,##0"),
    MAX('Table2'[Attribute])= "Pending",FORMAT(MAX('Table2'[Value]),"Fixed"),
    MAX('Table2'[Attribute])="Avg Sale",FORMAT(MAX('Table2'[Value]),"Percent"),
    FORMAT(MAX('Table2'[Value]),"General Number")
    )

    3. Result:

     

    Best Regards,

    Liu Yang

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

  • rajulshah's avatar
    rajulshah
    Resident Rockstar
    4 years ago

    Hello Anonymous ,

     

    This is only possible using Power BI measures and then formatting the measures as you want. We cannot have multiple data types for a single column.