Percentage Format in Query Editor & Model

Power Query
1

Hi all,

Quick questions on 1)M code and 2)Percentage format, please.

  1. I have a couple of columns in my data that hold decimal numbers, which I can change the format once loaded into the model. My attempt to change the column format to percentage while the data is being pulled is via this code:

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.From([Column1] * 100) & "%")

The downside of applying this code is that I am creating an additional column, so increasing the space. I am sure there has to be a better way of only transforming the decimal to a percentage format without adding columns? Potentially something around Table.TransformColumnTypes? not sure if that would enable me to do any calculation on the transformed col, or would that action change it into Text?

  1. Why when I convert the column into fixed decimal and change the format to percentage it shows more digits (screen below)? is there a correct way of displaying %?

image

Happy to go through any reading on the site.

Thanks
Hesham

2

Thanks for posting your question @Hesham. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

3

Hi @Hesham,

It will be helpful if you can add the sample file along with the source data(as you want to do it in power query).

But you can follow below steps to do the same if have only values in that column.

  1. Click on displayed type in front of header.
  2. Select % Percentage and you will have the output as required.
  3. Add then you can combine these steps into single step if you don’t want to add any extra steps.

image
image1920×1080 200 KB

1 Like
4

Hi @MK3010,

Apologies for not attaching a sample file. You can see in the sample file that col-copy is duplicate col to test my question. The data is sourced from excel file, which holds these columns in a Number format. When they get loaded into query editor, they are formatted as decimal numbers. When I change the Col5-copy into %, it moves the percentage 2 digits, which made me think I need to divide it by 100. The aim is to change col2,4 and 5 into %.

image

Assuming the above format step in query edit was done ( I am only changing the data type and not the format), is there a way for it to load into PBI with the % format? I understand the format is more about the appearance of that data only, but it was a question I had in mind.

Sample Percentage.pbix (20.1 KB)

Thanks

5

HI @Hesham

Could you please attached the source file also – decimal.xlsx

image
image1028×201 11 KB

6

Ops … attaching excel source file.

decimal.xlsx (9.9 KB)

7

Hi @Hesham,

You can use below power query. The problem to convert the number greater than 1 to percentage will exceed 100%, so you need to divide it by 100 and then convert it to percentage.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Anonymous\Desktop\decimal.xlsx"), null, true),    
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Col1", type number}, {"Col2", type number}, {"Col3", type number}, {"Col4", type number}, {"Col5", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Col5]/100,  Percentage.Type)
in
    #"Added Custom"

Below screen shot FYR. Also if it shows in decimal after loading then you change the format here as highlighted.

image
image1700×744 83.7 KB

8

Thank for your help @MK3010.