Need help to convert text YYYYMM to date.

[Nsimpson]

I have a text column YYYYMM that I need to convert to a date (ex: 201904 convert to 4/1/2019) in the query editor.  I would then like to filter the new date column to only include the last 24 months every time it is imported.  The table is very large and contains 7 years of data (over 15 million lines).  Can you help?

[parry2k · Accepted solution]

@Nsimpson add new custom colum in power query as below, replace column1 with your column name, once new custom column is added, change data type date

#date(
Number.FromText(Text.Start([Column1], 4)), 
Number.FromText(Text.Middle([Column1], 4,2)), 
1)

[MFelix]

Hi @Nsimpson create new column with the formula:

#date(Text.Start( [YYYTMM], 4), Text.End([YYYYMM],2), 1)

Then format column as date.

Regards.
MFelix