Change Date format in Query Editor

[LillyD]

Hi, 

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

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

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

[Alwyn1991]

@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]

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.

[LillyD]

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.

[gooranga1]

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.

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.

[LillyD]

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

Power BI

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.

[gooranga1]

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.

[LillyD]

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"))

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

[v-cherch-msft]

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