Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
selpaqm
Helper V
Helper V

Convert data to percentage in text typed column

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?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

 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.

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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

@Anonymous ,

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.

Anonymous
Not applicable

Thank You @Thejeswar 

v-yuta-msft
Community Support
Community Support

@selpaqm ,

 

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

Capture.PNG 

 

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
Not applicable

 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
Super User
Super User

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
Community Champion
Community Champion

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


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

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.