cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

date format issue - not corectly in all rows

hi,

i have date format column START (d. m. yyyy h:nn:ss)  and date format column END(d. m. yyyy h:nn:ss). I wanna compare them with exact but not in format (d. m. yyyy h:nn:ss) but only in  (dd/mm/yyyy).

In power query is data type : Date/TIME

I used functions

date = FORMAT(table[start];"DD-MM-YYYY")
date end = FORMAT(table[end];"DD-MM-YYYY")
date exact = EXACT(table[date];ld_dzv[date end]) .
And problem ? So many dates give me wrong position date and month like:
7.1.2020 (7. january.2020) return 1.7.2020 (1.july.2020) but
13.1.2020 (13. january.2020) return 13.1.2020 (13.january.2020)
 
I dont know where i m wrong
in the photo (red are wrong , green are OK)01.png02.png
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver II
Resolver II

After converting to only "Date" format, do some modeling steps on that column to split that by delimiter, splitting out the day, month, year and then concatening them in the required format as to dd/mm/yyyy or mm/dd/yyyy and then format the resultant column to date.

If you can share the table, can check and resolve for you.

View solution in original post

3 REPLIES 3
Highlighted
Super User III
Super User III

Hi @Mareno_123 

 

Check if your Regional settings are correct as most of the time this is the case with date formating issues.

 

image.png

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Highlighted
Resolver II
Resolver II

After converting to only "Date" format, do some modeling steps on that column to split that by delimiter, splitting out the day, month, year and then concatening them in the required format as to dd/mm/yyyy or mm/dd/yyyy and then format the resultant column to date.

If you can share the table, can check and resolve for you.

View solution in original post

Highlighted

for me works : 

ok Start = FORMAT(table[start].[Date];"YYYYMMDD")  

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors