Error on data -8,88178E-16 - output

Question

HI,&nbsp;I have this piece of code in&nbsp; PowerQuery EXCEL :letSource = Table.NestedJoin(#"0100 &amp; others", {"Customer"}, Modeller, {"Sold-to"}, "Modeller", JoinKind.LeftOuter),#"Expanded Modeller" = Table.ExpandTableColumn(Source, "Modeller", {"Customer name", "Outdoor Terms", "ZT04", "ZVER", "ZBND SS", "ZBND FW", "ZDAM", "ZT37", "ZT51", "ZT11", "ZG12"}, {"Modeller.Customer name", "Modeller.Outdoor Terms", "Modeller.ZT04", "Modeller.ZVER", "Modeller.ZBND SS", "Modeller.ZBND FW", "Modeller.ZDAM", "Modeller.ZT37", "Modeller.ZT51", "Modeller.ZT11", "Modeller.ZG12"}),#"Not in Modeller" = Table.ReplaceValue(#"Expanded Modeller",null,"Not in Modeller",Replacer.ReplaceValue,{"Modeller.Customer name"}),#"Filtered Rows" = Table.SelectRows(#"Not in Modeller", each [ZCPD] = 0 and [ZOP1] = 0 and [ZT63] = 0 and [ZCL2] = 0),#"Merged Queries1" = Table.NestedJoin(#"Filtered Rows", {"Sales Document", "Item (SD)"}, #"Price Date", {"Document", "Item"}, "Price Date", JoinKind.LeftOuter),#"Expanded Price Date" = Table.ExpandTableColumn(#"Merged Queries1", "Price Date", {"Pricing dt"}, {"Price Date.Pricing dt"}),#"Added Custom" = Table.AddColumn(#"Expanded Price Date", "Custom", each [Sales Document]&amp;"0"),#"Merged Queries2" = Table.NestedJoin(#"Added Custom", {"Custom"}, #"Price Date", {"Custom"}, "Price Date", JoinKind.LeftOuter),#"Expanded Price Date1" = Table.ExpandTableColumn(#"Merged Queries2", "Price Date", {"Pricing dt"}, {"Price Date.Pricing dt.1"}),#"Added Custom1" = Table.AddColumn(#"Expanded Price Date1", "Price date", each if [Price Date.Pricing dt]= null then [Price Date.Pricing dt.1] else [Price Date.Pricing dt]),#"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"Material"}, #"License and teamwear", {"H18340"}, "License and teamwear", JoinKind.LeftOuter),#"Expanded License and teamwear" = Table.ExpandTableColumn(#"Merged Queries", "License and teamwear", {"License(Net Pricing articles)"}, {"License and teamwear.License(Net Pricing articles)"}),#"Filtered Rows from License" = Table.SelectRows(#"Expanded License and teamwear", each ([#"License and teamwear.License(Net Pricing articles)"] = null)),#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows from License",{"License and teamwear.License(Net Pricing articles)", "Price Date.Pricing dt", "Custom", "Price Date.Pricing dt.1"}),#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Customer", "Sales Document", "Item (SD)", "Sales Document Type", "Requested Dlv. Date Header", "Requested Dlv. Date Item", "Price date", "Material", "List Price ZLI2", "Gross Value", "ZCL2", "ZCPD", "ZDAM", "ZBND", "ZFDD", "ZISO", "ZN30", "ZOP1", "ZT04", "ZT05", "ZT11", "ZT18", "ZT24", "ZT37", "ZT51", "ZT63", "ZG12", "ZVER", "Discount Rate-on", "Modeller.Customer name", "Modeller.Outdoor Terms", "Modeller.ZT04", "Modeller.ZVER", "Modeller.ZDAM", "Modeller.ZT37", "Modeller.ZT51", "Modeller.ZG12", "Modeller.ZT11", "Modeller.ZBND SS", "Modeller.ZBND FW"}),#"Replaced Value" = Table.ReplaceValue(#"Reordered Columns",null,0,Replacer.ReplaceValue,{"Modeller.ZT04", "Modeller.ZBND FW"}),#"Multiplication" = Table.TransformColumns(#"Replaced Value", let cols = {"Modeller.ZT04", "Modeller.ZVER", "Modeller.ZBND SS", "Modeller.ZBND FW", "Modeller.ZDAM", "Modeller.ZT37", "Modeller.ZT51", "Modeller.ZT11", "Modeller.ZG12"} in List.Zip({cols, List.Repeat({each _*100},List.Count(cols))})),#"Changed Type for decimal" = Table.TransformColumnTypes(#"Multiplication", {{"ZT04", type number} , {"ZDAM", type number} , {"ZT37", type number} , {"ZT51", type number} , {"ZT18", type number} , {"ZVER", type number}, {"ZT63", type number} , {"ZBND", type number} , {"Modeller.ZT04", type number}, {"Modeller.ZVER", type number}, {"Modeller.ZBND SS", type number}, {"Modeller.ZBND FW", type number}, {"Modeller.ZDAM", type number}, {"Modeller.ZT37", type number}, {"Modeller.ZT51", type number}, {"Modeller.ZT11", type number}, {"Modeller.ZG12", type number}}),#"Dif ZT37" = Table.AddColumn(#"Changed Type for decimal", "Dif ZT37", each [ZT37] - [Modeller.ZT37]),#"Dif ZT51" = Table.AddColumn(#"Dif ZT37", "Dif ZT51", each [ZT51] - [Modeller.ZT51]),#"Dif ZDAM" = Table.AddColumn(#"Dif ZT51", "Dif ZDAM", each [ZDAM] - [Modeller.ZDAM]),#"Dif ZVER" = Table.AddColumn(#"Dif ZDAM", "Dif ZVER", each [ZVER] - [Modeller.ZVER]),#"Dif ZT04" = Table.AddColumn(#"Dif ZVER", "Dif ZT04", each if [Sales Document Type] = "ZFUT" then [ZT04] - [Modeller.ZT04] else 0),#"Dif ZBND" = Table.AddColumn(#"Dif ZT04", "Dif ZBND", each if [Requested Dlv. Date Item] &lt; #date(2022, 06, 1) then [ZBND] - [Modeller.ZBND SS] else [ZBND] - [Modeller.ZBND FW]),#"Dif ZG12" = Table.AddColumn(#"Dif ZBND", "Dif ZG12", each [ZG12] - [Modeller.ZG12]),#"Dif ZT11" = Table.AddColumn(#"Dif ZG12", "Dif ZT11", each [ZT11] - [Modeller.ZT11]),#"Dif ZT18" = Table.AddColumn(#"Dif ZT11", "Dif ZT18", each if [Sales Document Type] = "OR" or [Sales Document Type] = "ZCQ" and [Modeller.Outdoor Terms] = "Yes" then [ZT18] - [Modeller.ZT18] else 0),#"Dif all" = Table.AddColumn(#"Dif ZT18", "Dif all", each [Dif ZT37] + [Dif ZT51] + [Dif ZDAM] + [Dif ZT04] + [Dif ZBND] + [Dif ZVER] + [Dif ZT11] + [Dif ZT18]),#"Changed Type" = Table.TransformColumnTypes(#"Dif all", {{"Price date", type date}, {"Dif ZT37", type number}, {"Dif ZVER", type number}, {"Dif ZG12", type number}, {"Dif ZT51", type number}, {"Dif ZDAM", type number}, {"Dif ZBND", type number}, {"Dif ZT18", type number}, {"Dif ZT11", type number}, {"Dif ZT04", type number} , {"Dif all", type number}})in#"Changed Type"&nbsp;as a result, those columns in that start with dif sometimes output this kinda error:&nbsp;I think is something related with type of data or rounding, do you know how to solve it?&nbsp;Kind regards,Marco Aguiar&nbsp;&nbsp;

vijay_a_verma · Accepted Answer

This is due to Floating Point Error.Whereever you are calculating Dif, over there you will need to use Number.Round (There are various rounding available but in this case just stick to default Number.Round). One example from your code, I am showing here. Remaining you can do on your own, here I am adding two decimal places. You can change decimal places to any number you want)#"Dif ZT37" = Table.AddColumn(#"Changed Type for decimal", "Dif ZT37", each Number.Round([ZT37] - [Modeller.ZT37],2))ORYou need not make above change, just select all Dif columns - Transform menu - Rounding - Round and give appropriate decimal places

Forum Discussion

Error on data -8,88178E-16 - output

2 Replies

Related content

Error In EventStream, Ingested Integer is Outputting as Blank

VAR and SWITCH Output issues

DMV Column Output Definitions and Options

error message

DateDiff Incorrect output