Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a column of many dollar values. I have confirmed none go below a penny. When summed, many values suddenly gain up to 30 decimal places. Rounding functions fail to clear the issue.
I have solved this problem with adding the "Precision.Decimal" argument to my List.Sum functions in a Group By step for one source where I need to preserve query folding, iaw another community forum post (and on a CSV source by setting the columns to fixed decimal/currency format). This is less than ideal as the Precision.Decimal argument is not visible in the GUI without opening advanced editor, and long story short such an arcane yet easy-to-stumble-into failure mode is going to be a political problem.
That said, my question is twofold:
1) Why does Power Query exhibit this behavior in floating point operations in the first place? Wasn't something like this a big scandal with the old Pentium I processors?
2) What do I tell the GUI-only, Power BI-skeptic analysts when they notice this happening, flip their $#!7 and try to go back to Excel?
Would you be willing to provide a pbix with the source file(csv?) so we can have a look at this please?
Not interested in the real data if it's private, just something that shows the issue please.