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
LillyD
Frequent Visitor

Change Date format in Query Editor

Hi, 

 

I am trying to change the date format of a column in query editor from the AM / PM (highlight) values shown below.

 

 

error3.PNG

 

If i change the column to Date/Time it errors as below 

 

Error4.PNG

 

 

I have managed to change the date format of the "Last Contact date" column within Modelling by using that formating by doing a search on the forum,

 

ButI would like the correct date to appear in the "Last Contact merged" column as all the others do currently it comes up with an error.

 

Thanks in advance

 

 

8 REPLIES 8
Alwyn1991
Frequent Visitor

@LillyD  have you tried in Query editor to replace the values i.e replace AM with blank the next step replace PM with blank then next step change the type?

gooranga1
Power Participant
Power Participant

Hi @LillyD,

 

What is being used to get that data in the first place as it seems to be mixed formats not one format for date. If you have access to the query that is pulling that data I would say it would be easier to get everything in one format there.

 

 

Hi @gooranga1 

Yes I tried that, but the format didnt change in the xls spread sheet when I corrected it.  It actually changed the correct formating and lefts the incorrect format the way it is.

 

In the spreasheet I have tried to copy the correcttly formated cell (dd/mm/yyyy hh:mm) using the format painter, and even though it updates the format of the cell the cell does not update.

 

error5.png

Hi @LillyD

 

Mmm with date formats like that in excel I'm afraid you are in a world of pain.

 

Using a couple of your examples you will have to modify those dodgy AM/PM formatted date strings in excel first and create your own new date column that you have calculated manually.

 

DatesFix.PNG

 

Column would be your new column.

 

Cell B2

=TRIM(LEFT(A2,LEN(A2)-11))

Cell C2

=TIMEVALUE(RIGHT(A2,11))

Cell D2

=IF(ISNUMBER(B2),VALUE(TEXT(B2,"mm/dd/yyyy")),DATE(RIGHT(B2,2)+2000,LEFT(B2,FIND("/",B2)-1),MID(LEFT(B2,FIND("/",B2,4)-1),FIND("/",B2)+1,LEN(B2))))

Cell E2

=IF(ISERROR(TEXT(D2,"dd/mm/yyyy") & " " & TEXT(C2,"HH:mm:ss")),A2,TEXT(D2,"dd/mm/yyyy") & " " & TEXT(C2,"HH:mm:ss"))

Copy those down and you should have a new 'date' column you can use.

 

 

 

Hi, @gooranga1,

 

Great thanks for that I am getting there! but...

 

The existing dates now show as null in Power bi from the orginal column, as they didnt transpose in Excel

 

Error6.PNG

 

Power BI

Error7.PNG

 

Any idea how i can combine the two columns please (sorry but all this is new to me), I trying to do as little manual work as possible in Excel as this will be an ongoing process for the migration.

hi @LillyD

 

It doesn't look like you have copied the formula to all fields. I see there are filters on your spreadsheet clear all filters and make sure all the formulas are copied to all the cells, for valid dates and and AM/PM dates.

Hi stil no luck I am afriad, here is the formala for row 5

 

Column AE =TRIM(LEFT(AA5,LEN(AA5)-11))

Column AF = TIMEVALUE(RIGHT(AA5,11))

 

Column AG =IF(ISNUMBER(AE5),VALUE(TEXT(AE5,"mm/dd/yyyy")),DATE(RIGHT(AE5,2)+2000,LEFT(AE5,FIND("/",AE5)-1),MID(LEFT(AE5,FIND("/",AE5,4)-1),FIND("/",AE5)+1,LEN(AE5))))

 

Column AH = IF(ISERROR(TEXT(AG5,"dd/mm/yyyy") & " " & TEXT(AF5,"HH:mm:ss")),AA5,TEXT(AG5,"dd/mm/yyyy") & " " & TEXT(AF5,"HH:mm:ss"))

 

error8.PNG

 

I have notice Cell in Col AA Row 2 is formatted as general text while cell in Col AA Row 5 is formated as a "Date / Tiime", but when converted to General it returns a result of 43289.6374

Hi @LillyD

 

You may try to use M Function in Query Editor. Here is the reference for you:

 

https://msdn.microsoft.com/en-us/query-bi/m/power-query-m-function-reference

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.