Forum Discussion
Rounding problem
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!
7 Replies
Hi dkat !
Seems correct to me, since 1.996 is rounding to 2.00 and 2.041 is rounding to 2.04It 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?
Also, I'm referring to the data in the table below the chart, not the values shown above the bar chart. Thanks!
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"
