Convert data to percentage in text typed column

[selpaqm]

Hi,

I have a column in my excel both text and percentages. however, when I use this excel as source, percentages are shown as 0,9838232 not 98% even excel is 98%. and cannot convert it to 98% because it says Expressions that yield variant data-type cannot be used to define calculated columns when i try to say if(table[column1]="No Data","No Data",table[column1]*100

is there any way to solve it?

[Juilee_Kuthe19]

I have excel sheet which is contain percentage data i.e 100%, 98.6% and text data i.e NA but after load in powerbi its shows in text datatype like 1.00, 980000000.2. I need to show as it is like excel sheet.

If you have solution please let me know.

Thank you

[Thejeswar]

@Juilee_Kuthe19 ,

In your case, NA is being considered a text and text cannot be automatically converted to %.

So while importing the data, in Power Query editor, replace the NA with BLANKS. (Not BLANK text but a blank space)

Then you should be able to set your column data type to percentage.

[Juilee_Kuthe19]

Thank You @Thejeswar 

[v-yuta-msft]

@selpaqm ,

Click on the data column, in modeling tab, change data type to whole number, then click the "percentage" button.

 

Community Support Team _ Jimmy Tao

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

[Anonymous · Accepted solution]

 if(table[column1]="No Data","No Data",table[column1]*100

here you are trying to add text data type and integer data type in single column which is not possible it will throw you error related to data type.

update your column by below dax

 if(table[column1]="No Data",Blank(),table[column1]*100)

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Thejeswar]

Hi,

You can do it in 2 different steps

1. Convert your column into a percentage

2. Create a new column to handle the No Data displayed

[az38]

Hi @selpaqm 

pick your percentage column in the right Field pane, go to Modeling ribbon and press % button. it will be displayed as percentage further

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn