Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to change YYYYMMDD into date format?

Hello guys,

 

How can i change the '20170104' from column IDT_DTM_ID into 04-01-2017?

 

Kind regards,

 

Rega

 

2017-01-05 17_13_40-Orakel_performance - Query Editor.png

2 ACCEPTED SOLUTIONS

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.

1.JPG

 

 

 

2. Right click on Table and choose new colum and apply the formula

2.JPG

 

 

Let me know if any help

 

 

 

View solution in original post

gpsuser
Advocate I
Advocate I

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:

Capture.PNG

 

[dateFormatted] will now be of type date,   formatted as:   dd Mmm yyyy

View solution in original post

16 REPLIES 16
Anonymous
Not applicable

Dear Baskar, I don't have modeling tab in my power bi.

 

I create a new column, see attached screenshot.

fizzi_0-1640774893345.png

 

Which formula has I to insert there to convert from column Date:20220105 (YYYYmmDDto a valid date format?

Many thanks for your support

 

kalyan
New Member

New column with below formula worked for me

DateKey = FORMAT(Table1[DateColumn1].[Date],"YYYYMMDD")

brentlightsey
Advocate II
Advocate II

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.

 

 

gpsuser
Advocate I
Advocate I

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:

Capture.PNG

 

[dateFormatted] will now be of type date,   formatted as:   dd Mmm yyyy

i have a similar issue and i keep getting an error, i tried almost everything still it shows error, image_2023-09-03_195313314.png

Hi,

I'm little late but might help others...

The fastest and easiest solution is to use the the "Column from example" and your dates should be recognized after you type in a transformation of one of your date.

So the folowing formula will not be needed. In case you don't like code, it is the best way.

 

If you like to write your own code, there is a M function you can use in customn column:

Date.From(Text.From([#"DATE AUGMENT ATION"])) if you have a numeric column

or 

Date.From([#"DATE AUGMENT ATION"])

(you also have the Date.fromText function, check out differences if needed)

Wishing you good transformation

I have a very simple way to do this in Power Query Editor (late, but might help others as @AilleryO also stated in his reply).

 

Three steps:

1. Change the date column type to Text

2. In case you have zero-values in your data (00000000), replace the 00000000 values with blank values, i.e. leave empty (this can be done in Transform tab --> Replace Values)

3. Change the date column type to Date

 

Note: If you don't need to do number 2., make a new step instead of replacing the previous one, otherwise it will skip the Text conversion and you'll end up of getting an error.

Baskar
Resident Rockstar
Resident Rockstar

Create new column using DAX

 

Create new calculated Column 

1.JPG

 

 

 

 

Anonymous
Not applicable

Hello,

 

Thank you for your message. i have cretated a new column and a new measure but i get the following error. there's something with the syntax for 4.

 

2017-01-06 16_05_25-Orakel_performance - Power BI Desktop.png

Create new column not measure.

Anonymous
Not applicable

OK, i created a custom column and these new column call DATUM.

 

Still i get a error of Expression.Error: The name 'DATUM' wasn't recognized...

 

2017-01-06 16_19_35-Orakel_performance - Query Editor.png

 

what did i wrong?

 

 

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.

1.JPG

 

 

 

2. Right click on Table and choose new colum and apply the formula

2.JPG

 

 

Let me know if any help

 

 

 

for using data model, you need to use following expression when you add custom column

 

=Date.FromText(
Text.Range([Input Date as Sting], 0,4) & "-"  &
Text.Range([Input Date as Sting], 4,2) & "-" &
Text.Range([Input Date as Sting], 6,2)
)

More information here:

 

https://msdn.microsoft.com/en-us/library/mt260703.aspx

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Table5 is your table Name.

 

Column1 is your column name.

 

Do u want to change YYYYMMDD to date ? right.

my formula ii give u the date column as date, yyyy-mm-dd

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.