Skip to main content

Power Query: Converting Monetary Values

Power Query has quickly become my favorite data transformation tool because of its fluid, interactive approach to describing data transformation. It isn't just easier to use that traditional ETL tools, it's also significantly more powerful and is a lot more forgiving.     Perhaps too forgiving...

Recently I worked on a solution for processing disparate spreadsheets that we receive from our partners. In theory it's simple enough: Combine the data tables, clean up the data, spit out the result.

The problem came in when we did checked the resulting data, and discovered that the monetary totals were way out of whack, and Power Query didn't report any errors in the data it had processed.

Consider the M query below, that imports the source data from a spreadsheet - take a close look at the ClaimAmount and VATOnClaimAmount columns. See anything wrong?


At first, the values appear to be normal monetary values, but on closer inspection we see that the value 131.58 is actually 131.58000000000001.  In excel this would amount to nothing more than a harmless rounding error, but in Power Query this can cause undesired results. Notice the type hint at the left side of the ClaimAmount column heading - it shows that Power Query is treating the column as the any type.

The problem comes in when you transform the columns to the decimal number type. Depending on locale settings, the value may be interpreted in any of the following ways:

  1. Period denotes a decimal place.  
    The resulting value is what we expected. Yay.

    or
  2. Period denotes a thousands separator.
    The resulting value is in the trillions.


Thankfully, the solution is fairly simple: Specify a locale during column conversion operations.
The column transform operation that the Power Query GUI creates for you will look something like this:
= Table.TransformColumnTypes(Source,{
{"ClaimAmount", type number}, {"VATOnClaimAmount", type number}
})
.. all you need to do is type in the locale at the tail of the TransformColumnTypes column:
= Table.TransformColumnTypes(Source,{
{"ClaimAmount", type number}, {"VATOnClaimAmount", type number} }, "en-US")
... and voila! Conversions treat the period as a decimal point, not a thousands separator.

Comments

Post a Comment

Popular posts from this blog

Reading Zip files in PowerQuery / M

Being a fan of PowerBI, I recently looked for a way to read zip files directly into the Data Model, and found this blog which showed a usable technique. Inspired by the possibilities revealed in Ken's solution, but frustrated by slow performance, I set out to learn the M language and write a faster alternative. UnzipContents The result of these efforts is an M function - UnzipContents - that you can paste into any PowerBI / PowerQuery report. It takes the contents of a ZIP file, and returns a list of files contained therein, along with their decompressed data: If you're not sure how to make this function available in your document, simply: Open up PowerQuery (either in Excel or in PowerBI) Create a new Blank Query. Open up the Advanced Editor  (found on the View tab in PowerBI). Copy-Paste the above code into the editor, then close the editor. In the properties window, rename the the function to  UnzipContents Usage Using the function is fairly straight for...
Read more

Power Query: Transforming YYYYMM dates (the quick way)

Accountants. Their unit of work seems to be the month, as if individual days don't exists, or don't count somehow. Nowhere is this better seen than in the notion of the accounting period , which all too often follows the form YYYYMM.  Try converting this directly into a date and Power Query starts making excuses faster than a kid with his hand caught in the cookie jar. The quick solution to this is to understand what Power Query's Table.TransformColumns does, and then leverage this knowledge to transform your YYYYMM values into proper date type columns. Table.TransformColumns As it's name suggests, this handy function allows you to convert the contents of a column from one type to another. The basic syntax is: = Table.TransformColumns( #"Your Source Table", { A list of tuples, specifying the columns and what functions to apply to each value} ) Lists {denoted by curly braces} are something you need to get comfortable with if you...
2 comments
Read more

Loading data from an Oracle source

Building Business Intelligence using the Microsoft BI stack in an organization that has an Oracle based transactional system is not uncommon, and here I'll outline a couple of tips and tricks that should ease the building of SSIS packages in that type of environment.   Attunity Components Attunity offer several nice solutions for getitng data out of Oracle and into SQL - one of which is their CDC (Change Data Capture) offering . If you don't have the budget or stomach for setting up CDC in an oracle environment, then your next best bet is to use the free Attunity oracle connectors for SSIS , which demonstrate a measurable performance boost.   The Oracle Index and Order By Gotcha In previous posts I mention the performance benefits of loading sorted data into target tables. I'm currently loading data from Oracle 10, which exhibits a rather strange characteristic... Oracle does not use make us of an index for ordering data .  In other words, Oracle only uses ...
Post a Comment
Read more