Forum Discussion
How to change YYYYMMDD into date format?
No No,
u r trying to create in power Query window. so only it throws error.
i was gave u DAX Query.
U have to create new column in Development environment .
1. Create via Modelling Tab.
2. Right click on Table and choose new colum and apply the formula
Let me know if any help
assume: [date] is in the following format: yyyymmdd
create a new column named [dateFormatted] and apply the formula as follows:
- dateFormatted = Date(Left([date],4),Right(left([date],6),2),right([date],2)
select the new column and change its type to date as follows:
[dateFormatted] will now be of type date, formatted as: dd Mmm yyyy
I also found that this simple formula would convert your date from a YYYYMMDD string to a true date. Add as a new column:
DateFormatted = Date.From([IDT_DTM_ID])
If your date, is stored as an integer, (DateKey column), and not text, a slight tweak on brentlightsey answer will do the trick:
Date.FromText( Number.ToText( [IDT_DTM_ID]))
You can then tweak the code in the advanced editor to add a data type conversion from text to a date value:
Original line:
#"Added Custom" = Table.AddColumn(MyDataSource, "DateFormatted", each Date.FromText( Number.ToText( [IDT_DTM_ID])))
Add type cast:
#"Added Custom" = Table.AddColumn(MyDataSource, "DateFormatted", each Date.FromText( Number.ToText( [IDT_DTM_ID])) type date)
You can do the type conversion in the Power Query Editor, but I like keeping the script as clean as possible and try to combine steps whenever possible.