Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have Excel as data source and date is coming as 20150714. How to parse or convert to date format?
And how to make month slicer?
Solved! Go to Solution.
Hi @Anonymous,
As the text value "20150714" is not formatted as standard date format, it's not able to convert it to Date data type directly.
In your scenario, you can try the DAX shared by @joerykeizer,
Also you can create a Custom Column in Query Editor follow below formula:
=Date.FromText(Text.Range([Column1],0,4)&Text.Range([Column1],4,2)&Text.Range([Column1],6,2))
Best Regards,
Qiuyun Yu
Hi @Anonymous,
As the text value "20150714" is not formatted as standard date format, it's not able to convert it to Date data type directly.
In your scenario, you can try the DAX shared by @joerykeizer,
Also you can create a Custom Column in Query Editor follow below formula:
=Date.FromText(Text.Range([Column1],0,4)&Text.Range([Column1],4,2)&Text.Range([Column1],6,2))
Best Regards,
Qiuyun Yu
Text To Date
=Date.FromText(Text.Range([Column1],0,4)&Text.Range([Column1],4,2)&Text.Range([Column1],6,2))
Number to Date
=Date.FromText(Text.Range(Number.ToText([Column1]),0,4) & (Text.Range(Number.ToText([Column1]),4,2)& (Text.Range(Number.ToText([Column1]),6,2))))
Just select the data type to "Date" by right clicking the column in Query ediotr to convert it into date column.
For a month Slicer, You can drag and drop the date field to the slicer visual and select only month.
Modeling => Data Type: Date = > "We can't automatically convert this column to date type"
Not sure if it's the best way, but you could create a new column like this:
Date = DATE(LEFT([DateText];4);MID([DateTest];5;2);RIGHT([DateText];2))
You specify which part of the text is year, month and day.
Btw you could also do this in the Query Editor by creating a new column:
=Date.FromText([DateText],"en-Us")
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |