Showing percentage values as numbers

[jammpr]

Hello,

I have a simple excel spreadsheet that I've imported in BI.  I have a column of percentages in my excel sheet (no calculations) that are showing up in BI as numbers.  I cannot seem to it get it to show the percentage sign.  For example 244% is showing up as 1.

Any help would be appreciated.

thanks!

[parry2k · Accepted solution]

You only need to replace the value for NA, not for other columns and add this step befor you change the type to percent (although I don't think that will make a difference)

Basically you are replacing NA with 0 value and then change type to percent.

#"Replaced Value" = Table.ReplaceValue(#"Changed Type1","NA","0",Replacer.ReplaceValue,{"Percentage of Increase"}),

[MarcelBeug]

The data type of your column is text, probably because of the NA's.

Via "Edit Queries", you can replace NAs with nulls and then replace the data type with (Decimal) Number and Format Percentage.

[jammpr]

Hi Marcel, 

Thank you for your reply.

I followed your instructions, but now 244 is showing up as 244,400.00% and the correct value is 244%. How I can fix this?

[MarcelBeug]

Can you share your code (go to the Advanced Editor, copy everything and paste it here)?

If I copy your picture and enlarge it to 400%, I see 4x Changed Type steps, 3x Replace Value steps and 1 Trimmed text.

That's not what I suggested.

[jammpr]

Hi Marcel,

Code:

let
Source = Excel.Workbook(File.Contents("C:\Users\pr00023\OneDrive - \Informes\Sharepoint Reports\Total of users\Total of users.xlsx"), null, true),
totalofusers_Table = Source{[Item="totalofusers",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(totalofusers_Table,{{"Year", Int64.Type}, {"Total of Users", Int64.Type}, {"Percentage of Increase", type number}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Percentage of Increase", Percentage.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1",2.44,244,Replacer.ReplaceValue,{"Percentage of Increase"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",0.12,12,Replacer.ReplaceValue,{"Percentage of Increase"})
in
#"Replaced Value1"

Screenshots:

Home

Modeling

Dashboard

Editor Query

[MarcelBeug · Accepted solution]

Apparently you got rid of the "NA" in your Excel sheet, otherwise the "Percentage of Increase" field wouldn't default to type number in step "Changed Type".

So you created yourself a different situation, making my first suggestions invalid.

I never suggested you to change 2.44 to 244 and 0.12 to 12: so far you ddn't provide this information and this is exactly why you got the wrong percentages.

With this new situation, you don't need to do anything in the query editor.

You can just change the format of the "Percentage of Increase" field in the modeling tab to "Percentage".

Please notice the value of providing all relevant and accurate information when posting a question.

[parry2k · Accepted solution]

You only need to replace the value for NA, not for other columns and add this step befor you change the type to percent (although I don't think that will make a difference)

Basically you are replacing NA with 0 value and then change type to percent.

#"Replaced Value" = Table.ReplaceValue(#"Changed Type1","NA","0",Replacer.ReplaceValue,{"Percentage of Increase"}),