Rounding problem

[dkat]

Hi all,

I'm having a problem with a financial report. For some reason, three months of the year (Oct-Dec) in my report are rounding my currency values, while the rest of the months of the year are not. For example, see the table below, September numbers look like this:

While numbers in Oct-Dec round to .00, like this:

All the numbers are pulling from the same spreadsheet. Any idea why this would be happening and how to stop it? Thanks in advance for your assistance!

[dkat]

The Advanced Editor code is below. I did find one thing interesting. I underlined three values below. Originally those three months said type 'date' vs. 'number'. I did edit them so they now all say type 'number' to match the other months, but it did not solve the problem unfortunately. Do you see anything else out of whack?

let
Source = Excel.Workbook(File.Contents("C:\Users\Deanna Katchur\Documents\Financial Analytics\Final P&L USWIG v2.xlsx"), null, true),
#"12 Month Trend_Sheet" = Source{[Item="12 Month Trend",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"12 Month Trend_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"USWIG", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type date}, {"Column15", type date}, {"Column16", type date}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",7),
#"Promoted Headers1" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Account No.", Int64.Type}, {"Type", type text}, {"Subtype", type text}, {"Name", type text}, {"1/1/2022", type number}, {"2/1/2022", type number}, {"3/1/2022", type number}, {"4/1/2022", type number}, {"5/1/2022", type number}, {"6/1/2022", type number}, {"7/1/2022", type number}, {"8/1/2022", type number}, {"9/1/2022", type number}, {"10/1/2022", type number}, {"11/1/2022", type number}, {"12/1/2022", type number}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([#"Account No."] <> null)),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Account No.", "Type", "Subtype", "Name"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Value", Currency.Type}, {"Account No.", type text}})
in
#"Changed Type2"

[PabloDeheza]

Hi @dkat !
Seems correct to me, since 1.996 is rounding to 2.00 and 2.041 is rounding to 2.04

[dkat]

It is rounding correctly, but I don't want it to be rounding the values. January-September months are not rounding the data, Oct-Dec months are. Why would 1/3 of the report be showing rounded values?

[dkat]

Also, I'm referring to the data in the table below the chart, not the values shown above the bar chart. Thanks!

[PabloDeheza]

oh got it, have you double check that the data in your model is not rounded for those months as well?

[dkat]

When I click on the Data tab, it shows that data is rounded there for the months of Oct-Nov, but it's all in the same column so I can't figure out why it would round some months and and not others. When I look in the Excel source document that the numbers are pulling from, the data is not rounded there.

[PabloDeheza]

If it is rounded in the data tab but not in the excel sheet then the problem must be somewhere in the middle, like power query. Take a look at the steps you have applied in Power Query, maybe one of those steps is rounding those numbers