Mark White's BI Blog

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...

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...