Forum Discussion
error message when changing column type from text to date - Quarter only
Hi,
I'm new to Power BI. In my excel file, there are two text columns: FY and Qtr. FY column only has "2023". Qtr column has "Q1", "Q2", "Q3" and "Q4".
When I transformed FY column type to Date (then tranform Year --> Year), it returned 1905. But I think I resolved it by Replacing all "1905" with "2023".
When I transformed Qtr column type to Date, it returned Error in the entire column. I tried duplicate column and add new custom column, all returned the same.
How do I fix this?
Many thanks in advance!
Hi QLisa ,
One possible reason why you are getting errors when transforming the Qtr column to date is that Power BI does not recognize the format “Q1”, “Q2”, “Q3” and “Q4” as valid dates. You need to provide more information for Power BI to convert the text to date, such as the year and the month.
One possible solution is to create a new custom column that combines the FY and Qtr columns, and then use the DATEVALUE function to convert the text to date.
Date.FromText - PowerQuery M | Microsoft Learn
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi QLisa
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523This is what I see when I'm in the transform step in Power BI:
Hi QLisa ,
One possible reason why you are getting errors when transforming the Qtr column to date is that Power BI does not recognize the format “Q1”, “Q2”, “Q3” and “Q4” as valid dates. You need to provide more information for Power BI to convert the text to date, such as the year and the month.
One possible solution is to create a new custom column that combines the FY and Qtr columns, and then use the DATEVALUE function to convert the text to date.
Date.FromText - PowerQuery M | Microsoft Learn
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you! Anonymous ,
