Forum Discussion
Convert Text Date to Date format
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?
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.
3 Replies
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
Hi,
In the Query Editor, filter out anything starting with Q and FY. Right click on the column and select Date.
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.
