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 Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Top Solution Authors
Top Kudoed Authors