Column with numbers and percentages

[karenrodang]

Hello,

I have a table with columns that have numbers and percentages on the same column, but I loose the format from the original table in excel.

I'm trying to make it look exactly the same because the percentages loose the % format.

Example:

Concept	Quarter	Q1 2018	Q2 2018	Q3 2018
Revenue	Q1 Locked Forecast	286,249,297	221,583,865	302,742,560
Revenue	Q2 Locked Forecast	0	212,961,075	274,069,215
Revenue	Q3 Locked Forecast	0	0	252,017,930
Revenue	Q4 Locked Forecast	0	0	0
Revenue	8 Quarter	308,232,834	221,046,921	222,337,635
Corp Adj Net Core Op Income	Q1 Locked Forecast	8,462,674	1,812,654	13,701,742
Corp Adj Net Core Op Income	Q2 Locked Forecast	0	774,263	7,805,105
Corp Adj Net Core Op Income	Q3 Locked Forecast	0	0	10,025,566
Corp Adj Net Core Op Income	Q4 Locked Forecast	0	0	0
Corp Adj Net Core Op Income	8 Quarter	12,535,203	668,611	7,242,886
Core Adj Net Op Income %	Q1 Locked Forecast	2.96%	0.82%	4.53%
Core Adj Net Op Income %	Q2 Locked Forecast		0.36%	2.85%
Core Adj Net Op Income %	Q3 Locked Forecast			3.98%
Core Adj Net Op Income %	Q4 Locked Forecast
Core Adj Net Op Income %	8 Quarter	4.07%	0.30%	3.26%
Revenue	Q1 Locked Forecast	156,110,995	92,610,545	127,754,216
Revenue	Q2 Locked Forecast	0	80,130,475	112,922,580
Revenue	Q3 Locked Forecast	0	0	69,591,743
Revenue	Q4 Locked Forecast	0	0	0
Revenue	8 Quarter	153,922,649	75,511,999	72,020,731

Is there a way to fix this? Tried formating the column in excel as text and didn't work.

Thanks

Karen

[v-yuta-msft · Accepted solution]

Hi karenrodang,

To achieve two different format in one column, you should convert type of values to text, click Query Editor-> Add Columns-> Custom Column and input M code like below:

= if [Q1 2018] = null then null else if [Q1 2018] <= 1 then Number.ToText([Q1 2018] * 100) & "%" else Number.ToText([Q1 2018])

 

Regards,

Jimmy Tao

[karenrodang]

Hi Jimmy,

Thank you very much this definetely worked!!! Awesome!! 

Have one last question, now how do I reduce the number of decimals showing in the column? Can I do it in PBI or do I have to do that first in Excel?

Thank you!!!

[prihana]

This is not recommended at all. Please use seperate columns.