Forum Discussion
Rounding problem
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"