I have a decimal number that's rounding to the nearest whole number and I can't figure out why

[JClishe]

I've imported an Excel spreadsheet into Power BI and I have a report built on several number columns in the spreadsheet. All of the columns are formatted the following way in Excel:

- Number

- 1 decimal place

The values are displaying accurately in Excel.

In Power BI, I have the columns formatted as follows:

- Data Type: Decimal Number

- Format: Decimal Number

- Decimal Places: 1

All of the columns are displaying accurately in Power BI except for 1, it's rounding to the nearest whole number. I've manually changed the data in Excel to random decimal numbers, saved it, refreshed Power BI, and it's always coming in rounded to the nearest whole number. Any ideas why?

[roberto_wilson]

Hi! did anyne find a solution to this issue? I am having the same kind of problem.
Thanks!

[v-yulgu-msft]

Hi @JClishe,

I have tested this scenario, but I could not replicate your issue.

Which version of PowerBI do you use (mine is Version: 2.40.4554.421 64-bit (October 2016))? After importing data from Excel, did you make any settings to data? The round number occurs in table view or in visual? Also, you said "all of the columns are displaying accurately in Power BI except for 1‘, do you mean if setting decimal place to 2 or other values, this issue disappear? 

If possible, please share some sample data for further analysis.

Best regards,
Yuliana Gu

[JClishe]

Hi Yuliana

I'm using Power BI Desktop Version: 2.40.4554.463 64-bit (October 2016)

My data contains 8 columns I imported from Excel that are all numerical values. When I imported them into Power BI, I formatted all 8 columns as Data Type: Decimal Number, Format: Decimal Number, Decimal Places: 1.

7 of the columns are displaying accurately in Power BI, meaning that if the data from Excel was 1.7, then it's displaying in Power BI as 1.7. However 1 of the columns is rounding to the nearest whole number, meaning if the data in Excel is 1.7 it's displaying in Power BI as 2.0. It shows 2.0 in both the table view and in the visual.

I can change the sources values in Excel to any decimal number, and when I refresh Power BI it always rounds this one particular column to a whole number, while the other 7 columns are displayed correctly as decimal numbers even though all 8 columns are formatted the same in table view.

[Stachu]

can you share the M query syntax that imports the data from Excel?

[JClishe]

I don't know what that means, can you provide more details please? 

[Stachu]

in PowerBI go to 'Edit Queries', then in the Home tab go to 'Advanced Editor' of the query that imports from Excel

copy the code here

[JClishe]

Got it, thanks! The "Night" column is the one that's rounding to the nearest whole number. Specifically it's 1.8 in Excel and Power BI shows 2.0. 

let
Source = Excel.Workbook(File.Contents("C:\Users\jason\OneDrive\Documents\Aviation\PilotLogbook.xlsx"), null, true),
#"Jason Clishe Pilot Logbook_Sheet" = Source{[Item="Jason Clishe Pilot Logbook",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Jason Clishe Pilot Logbook_Sheet"),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Aircraft ID", type text}, {"From", type text}, {"To", type text}, {"Route", type text}, {"Dual Recevied", type number}, {"PIC", type number}, {"Night", Int64.Type}, {"Cross Country", type number}, {"Instrument", Int64.Type}, {"Total Time", type number}, {"Dual XC", type number}, {"Solo XC", Int64.Type}, {"XC > 150 nm", Int64.Type}, {"Night XC", Int64.Type}, {"Night XC > 100 nm", Int64.Type}, {"Distance", Int64.Type}, {"Day Takeoffs", Int64.Type}, {"Day Landings Full Stop", Int64.Type}, {"Night Takeoffs", Int64.Type}, {"Night Landings Full Stop", Int64.Type}, {"Towered Takeoffs", Int64.Type}, {"Towered Landings Full Stop", Int64.Type}, {"All Landings", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Night XC > 100 nm", "Night cross country flights greater than 100 nm"}, {"XC > 150 nm", "Cross country flights greater than 150 nm"}})
in
#"Renamed Columns"

[KHorseman]

There's a step in your query that sets a bunch of columns to integer data types. "Night" is among them:  {"Night", Int64.Type},

[JClishe]

Should it be something different? 

Keep in mind the only thing I've done is opened an Excel file from Power BI Desktop. If this requires something to be customized somewhere in order to prevent it from rounding, I don't know how to do that. 

[Sean]

@JClishe Go to the Query Editor by clicking Edit Queries in PBI

Select the Column(s) in question - Home Tab - Data Type: select Decimal

This will change the code to => {"Night", type number}

[JClishe]

Ugh, still no joy. I changed the number columns to decimal in the Query Editor, but that one particular column is still coming into Power BI as whole numbers. I've messed with the data in Excel and everytime I refresh Power BI it's still always rounding to the nearest whole number.

[Sean]

[KHorseman]

@JClishe and when you load that query, the "Night" column comes in as a whole number?

[JClishe]

That's correct, the numbers in the Night column are coming into Power BI as whole numbers, even though they are decimals in Excel. 

[GabrieleVLN]

Hello guys,

I have a very similar issue on Power BI.

I have the following dataset which I put on table visual:

Country    SLA

Canada    2.40

Canada    2.40

Canada    2.40

Canada    2.40

Canada    2.50

However, when I create a matrix and take the average of these values, I get:

Country   SLA

Canada    2.45

However, the average of my dataset is 2.42

The data of "SLA" are set to be Decimal Number (type and format) with two decimals. Do you know how can I fix this?

Your support is highly appreciated! 

Regards,

Gabriele

[KHorseman]

Can you show us how your query steps read now that you've made changes?

[JClishe]

Yup:

let
Source = Excel.Workbook(File.Contents("C:\Users\jason\OneDrive\Documents\Aviation\PilotLogbook.xlsx"), null, true),
#"Jason Clishe Pilot Logbook_Sheet" = Source{[Item="Jason Clishe Pilot Logbook",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Jason Clishe Pilot Logbook_Sheet"),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Aircraft ID", type text}, {"From", type text}, {"To", type text}, {"Route", type text}, {"Dual Recevied", type number}, {"PIC", type number}, {"Night", Int64.Type}, {"Cross Country", type number}, {"Instrument", Int64.Type}, {"Total Time", type number}, {"Dual XC", type number}, {"Solo XC", Int64.Type}, {"XC > 150 nm", Int64.Type}, {"Night XC", Int64.Type}, {"Night XC > 100 nm", Int64.Type}, {"Distance", Int64.Type}, {"Day Takeoffs", Int64.Type}, {"Day Landings Full Stop", Int64.Type}, {"Night Takeoffs", Int64.Type}, {"Night Landings Full Stop", Int64.Type}, {"Towered Takeoffs", Int64.Type}, {"Towered Landings Full Stop", Int64.Type}, {"All Landings", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Night XC > 100 nm", "Night cross country flights greater than 100 nm"}, {"XC > 150 nm", "Cross country flights greater than 150 nm"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Night", type number}, {"Instrument", type number}, {"Solo XC", type number}, {"Cross country flights greater than 150 nm", type number}, {"Night XC", type number}, {"Night cross country flights greater than 100 nm", type number}, {"Distance", type number}, {"Day Takeoffs", type number}, {"Day Landings Full Stop", type number}, {"Night Takeoffs", type number}, {"Night Landings Full Stop", type number}, {"Towered Takeoffs", type number}, {"Towered Landings Full Stop", type number}, {"All Landings", type number}})
in
#"Changed Type1" 

[LNewNCC]

Hi - I'm experiencing the same issue but cannot find a solution that works anywhere - did you ever get this resolved?

I have data in Excel that is pulling through all fine apart from one column.  This column has numbers such as 70.60 in it but it is displaying in Query Editor as 71.00.  This is in spite of any and all changes I have made.  The columns either side of this column are the same - decimal numbers displayed to 2 dps - and they are displaying correctly.  This column however is just refusing to pull through the decimal number as it should be.

The type is definitely set as "type number" when I look under Advanced Editor (rather than Int64).

Data Type in Query Editor is Decimal Number

In Desktop: Data type showing as Decimal Number, Format: Decimal number, Decimal places set at 2. But still the figures in this particualr column are rounding up.... 78.00 instead of 77.60.

The excel data sheet it is pulling these figures from are all formatted the same - Number to 2dp.

Please can someone shed some light on this - it is causing a headache with RAG status cards I have.