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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
gssarathkumar
Frequent Visitor

Working on Inconsistent Dates in Power Query

Hi, I am working on the below inconsistent date formats in power query and looking for a solution.

The below table contains May month date but not in the consistent. Hence while changing the data type, affecting the dates.

 

 

gssarathkumar_0-1698321900964.png

created
2021-09-05 10:15:45.0000000
2021-10-05 17:32:15.0000000
2021-10-05 17:58:15.0000000
16-05-2021 12:45:31
17-05-2021 17:31:12
18-05-2021 15:32:55
19-05-2021 08:54:23
20-05-2021 09:56:26


Please have a check on the first three rows. Instead of considering 9th May, 10th May, it is considered as 5th Sep, 5th Oct respectively which yeilds wrong output.

gssarathkumar_1-1698322099821.png


is there any solution to fix this Date formatting?

@amitchandak , @Greg_Deckler @lbendlin , @DesktopOwl @Goodlytics4U @help Ahmedx @Ahmedx @lbendlin : Please help.

5 REPLIES 5
Ahmedx
Super User
Super User

pls try this

try Date.FromText(
Text.Split(Text.From(
    [Column1])," "){0},[Format = "yyyy-dd-MM", Culture = "en-EN"]) otherwise
    Date.FromText(
Text.Split(Text.From(
    [Column1])," "){0},[Format = "dd-MM-yyyy", Culture = "en-EN"])

Screenshot_2.png

ppm1
Solution Sage
Solution Sage

You could also use a custom column (or custom transform) with this expression.

 

= if Text.Contains([DateTime], ".") then DateTime.FromText([DateTime], [Format="yyyy-dd-MM HH:mm:ss.fffffff"]) else DateTime.FromText([DateTime], [Format="dd-MM-yyyy HH:mm:ss"])

 

ppm1_0-1698323895958.png

 

Pat

 

Microsoft Employee

Hi @ppm1 ,

 

Acutually your solution helps, however i observed the source data also contains few rows in the below format. when i apply the same logic, it throws error as it doesn't contain the suffix.

 

gssarathkumar_0-1698327076481.png

 

Here is the sample data: Can you please help fixing this issue?

DateTime
2022-08-29 06:55:41
2022-10-31 06:05:43

You can first do a step to extract the text before delimiter of "." (to get rid of the .000) and then use this try ... otherwise expression

 

= try DateTime.FromText([created], [Format="yyyy-dd-MM HH:mm:ss"]) otherwise DateTime.FromText([created], [Format="dd-MM-yyyy HH:mm:ss"])

 

Pat

Microsoft Employee
brokencornets
Helper III
Helper III

First, split column by delimiter (space)

 

Then select Add Column > Column From Examples

 

Type in the correct date format for the first couple - you'll notice that it autopopulates but it's wrong for the second date format. But if you then type in a couple of examples for the new date format Power BI should be smart enough to work out the pattern.

 

brokencornets_0-1698323688288.png

 

 

Once you've done that you can concatenate back to date/time if you prefer.

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.