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 want to harness the under-the-hood power of the M language. The example below lists two tuples - for ReportedDate and LossDate respectively. On both columns, the function Date.From is invoked, which attempts to convert in input value to a date type.

= Table.TransformColumns( #"Your Source Table",{
{"ReportedDate", Date.From}, {"LossDate", Date.From} })

This works fine for normal dates, but for YYYYMM dates we need to cook up our own function:

= Table.TransformColumns(#"Replaced Value",{
{ "Date",
each Date.From(Text.From(_) & "01") }})

The secret here is in understanding what each does.
It is shorthand for creating a nameless (or anonymous) function, where the underscore character represents the single parameter/argument being passed in by the caller.

The example above effectively says:  "For each value in the Date column, convert the value to text (Text.From) then concatenate the string 01  (to create a text value of the form YYYYMM01) , then convert that text to a date type (Date.From) and put the result back into the Date column."

Give it a try!