Decimal numbers from Excel automatically getting rounded and converted to integers in PowerBI

[Cado_one]

Hi all,

I know this topic has already been treated. However I opened every similar topics and none of the solutions given are working for me.

Let's explain, I got 8 columns in an excel file which contains either integers, decimals numbers or void. The data type in Excel is standard. When importing in Power BI, 6 of these columns are doing well but 2 of them are getting rounded and considered as integers. (for example 1,75 becomes 2 and 0,25 becomes 0)

Problem is : I want this two columns to give me decimals as we can see them in the Excel file.

Here is what I tried to fix it :

- Change the two column's Data type for Text in PBI and in Power Query editor (the 6 working columns have a text data type) 

- Change the two column's Data type for Decimal in PBI and in Power Query editor with 2 decimal digits, it gives me only zeros after the coma (for example 1,75 in excel becomes 2,00 in PBI)

- Change the two column's Data type for Text in Excel (in other topics this solution seems to work for some people but it isn't for me and changing data type on excel is not okay for me because the source file will be often changed and I can't have to change data type each time)

- For information there are Decimals in the first 200 records so the problem doesn't come from this side.

None of the above solutions did the job and I have no idea of what I could try more. That's why I post the topic here with a strong hope that someone will be able to help me.

Sorry for my bad english, I'm working on it ^^

Thanks for consideration,

Cado

[amitchandak · Accepted solution]

@Cado_one , In Edit Query/ Data Transformation , Right click on table And there option for Advance Editor

You will script like

let
Source = Excel.Workbook(File.Contents(".....ig.xlsx"), null, true),
Sales_Sheet = Source{[Item="Sales",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sales_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Order No", Int64.Type}, {"Item ID", Int64.Type}, {"Sales Date", type date}, {"Deilvery Date", type date}, {"Customer Id", Int64.Type}, {"City Id", Int64.Type}, {"Qty", Int64.Type}, {"Price", Int64.Type}, {"COGS", Int64.Type}, {"Discount Percent", Int64.Type}}),

Something like
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"COGS", "COGS Unit"}, {"Deilvery Date", "Delivery Date"}})
in
#"Renamed Columns"

Here Go ahead a change data type to number or Decimal or double like {"COGS", type number}

[Cado_one]

Thank you very much @amitchandak you solved my issue !

I hope this will help other people having the same problem 🙂