Power Query date format

Question

I am using Power Query to access my PostgreSQL database and filter my data by certain date parameters. However, for one of my database tables the date format is YYYYMM (201510).

Is it possible to convert this format to an actual date format?

Answer 1

Power Query recognizes YYYY-MM or YYYYMMDD as valid date formats, but not YYYYMM. Here's a solution inserting a hyphen then changing types:

1. Split the text by number of characters, 4
2. Delete the automatic number type inference step.
3. Merge the columns using a custom separator -
4. Change type to date

Here's a simple example:

let
    Source = Csv.Document("201510
201502"),
    SplitColumnByPosition = Table.SplitColumn(Source,"Column1",Splitter.SplitTextByPositions({0, 4}, false),{"Column1.1", "Column1.2"}),
    MergedColumns = Table.CombineColumns(SplitColumnByPosition,{"Column1.2", "Column1.1"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
    ChangedType = Table.TransformColumnTypes(MergedColumns,{{"Merged", type date}})
in
    ChangedType

Answer 2

Please try:

=DATE(LEFT(A1,4),RIGHT(A1,2),1)

Answer 3

select to_date('201510', 'YYYYMM');
  to_date   
------------
 2015-10-01

Answer 4

In this case I prefer to create a new custom column and delete the original column afterwards. In your case the formula for the custom column would look like this:

=Text.Range([DateColumn],0,4) & "-" & Text.Range([DateColumn],4,2) & "-01"

After adding the custom column you can change it's format to Date.

For further reference check the M Formula reference: https://msdn.microsoft.com/en-us/library/mt211003.aspx

Answer 5

Yes that's possible. Since you import the value as a number you:

1. Transform your value to text
2. Use the Date.FromText function.

The code to do that is:

= Date.FromText( Text.From( 201510), [Format = "yyyyMM"])

Even though this is the Date.FromText functions, you can find the different format strings for this here: https://powerquery.how/date-totext/