cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cado_one
Helper V
Helper V

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

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

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@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}



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
amitchandak
Super User IV
Super User IV

@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}



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Thank you very much @amitchandak you solved my issue !

 

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors