Convert Text Date to Date format

[PBI_newuser]

Hi,

I have an excel file consisting "Period" column in text format.

How can I read only the month rows (Exclude Q1 19) and convert to date?

[Anonymous · Accepted solution]

Hi @PBI_newuser 

In addition to  Ashish_Mathur 's reply. I build a column like yours to have a test.

If we only remove all rows with Q and FY and change the column type to Date, you column will show as below.

The date may show wrong result.

So we need to add one step: replace 18 as 2018 and replace 19 as 2019 then change the type as Date.

The result is as below.

Select Colse&Apply and you can change the Format of this column in Column Tools.(Set Format as "yyyy-mm")

Due to date type, the real value for "yyyy-mm" is "yyyy-mm-dd". EX: 2018-10 = 2018/10/01.

You can download the pbix file from this link: Convert Text Date to Date format

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Ashish_Mathur]

Hi,

In the Query Editor, filter out anything starting with Q and FY.  Right click on the column and select Date.

[amitchandak]

@PBI_newuser , I Power Query, Create a Column Like this and remove Q(Qtr) and F(FY) Columns

New Col = Text.Start([COl1],1)

Then You can create date like with this given format

Date = "01 " & [Col1]  // Mark this as date column