Table Formatting

[Anonymous]

Hi,

I have taken this table from Excel & cannot use 'transpose' as this is the layout required - How do I edit this query so that the '%' rows are in percentage format & the Jul-21 list is in this order: 

Revenue, Gross Profit, GP%, Overheads, Operating Profit, OP%, EBITDA, EBITDA %, EBITDA adj, EBITDA adj %?

Thanks

[bchager6 · Accepted solution]

Here's a solution:

1. Using a table with just the first and second columns, and just the Revenue, GP%, and Gross Profit rows, I created a conditional column named "Sort Column" in the Query Editor, assigning 1 to Revenue, 2 to Gross Profit, and 3 to GP%
   
   
2. Then I created a custom column to convert any values in the Actual Month column aligned to the GP % row to a percentage: =if[#"7/21/2021"] = "GP %" then Number.ToText([Actual Month], "p") else [Actual Month]
3. Then I removed the Actual Month column and renamed the Custom column to Actual Month
4. After applying those steps, I closed the query editor and sorted the 7/21/21 column by the Sort Column

Here's the end result. It works, but all the values are of the text data type. But maybe that doesn't matter here because we wouldn't want to sum decimal numbers with percentages anyway.

I hope this helps. If it does, please mark it as the solution.

[Anonymous]

@bchager6 @V-pazhen-msft Thats perfect, thank you very much for your help!

[Anonymous]

@V-pazhen-msft I don't suppose you know what to add to the measure so that the percentage rows show to the nearest percent (0 d.p.)? Formatting to 0 d.p. in 'Measure tools' only applies to the non-percentage values. Thanks

[V-pazhen-msft · Accepted solution]

@Anonymous 
1. To sort the column, you can add an index then sort using conditional column as @bchager6 suggested. 

2. In order to change some value to %, you need to create a measure for each column, just replace the referred column to the corresponding column name. For example:

Anual month measure = IF(RIGHT(MAX([Type]),1)="%",FORMAT(MAX([Anual Month]),"Percent"),MAX([Anual Month]))

Anual Year measure = IF(RIGHT(MAX([Type]),1)="%",FORMAT(MAX([Anual Year]),"Percent"),MAX([Anual Year]))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[bchager6]

@V-pazhen-msft  I like this. It preserves the data type and only relies on a % suffix. 👍

[bchager6 · Accepted solution]

Here's a solution:

1. Using a table with just the first and second columns, and just the Revenue, GP%, and Gross Profit rows, I created a conditional column named "Sort Column" in the Query Editor, assigning 1 to Revenue, 2 to Gross Profit, and 3 to GP%
   
   
2. Then I created a custom column to convert any values in the Actual Month column aligned to the GP % row to a percentage: =if[#"7/21/2021"] = "GP %" then Number.ToText([Actual Month], "p") else [Actual Month]
3. Then I removed the Actual Month column and renamed the Custom column to Actual Month
4. After applying those steps, I closed the query editor and sorted the 7/21/21 column by the Sort Column

Here's the end result. It works, but all the values are of the text data type. But maybe that doesn't matter here because we wouldn't want to sum decimal numbers with percentages anyway.

I hope this helps. If it does, please mark it as the solution.