Relationship on date/time column with different formats

[Anonymous]

Hi,

I'm simply trying to relate column C from "Native Load 2019" with the other excel file "DART 2019 with correct Date_Example" so that the correct values align with the correct date/time. I've tried doing this in Power BI by manipulating the date formats and creating a relationship on them but am struggling. At first I tried sharing my Power BI but I think it may be easier to just supply you with the excel sheets so that you can work thorugh the relationship from scratch. 

Thanks in advance, I'll appreciate any support you can give!

https://www.dropbox.com/scl/fi/e4a7fjhn1aledjge4sxrk/DART-2019-with-correct-Date_Example.xlsx?dl=0&r...

https://www.dropbox.com/scl/fi/2i5g6zwloublcfg885heu/Native_Load_2019.xlsx?dl=0&rlkey=ej4ezqvg59w3pe...

[Icey · Accepted solution]

Hi @Anonymous ,

The issue is that you can't convert [HourEnding] column in 'Native Load Report' table from "Text" to "Date/Time", right?

If so, try this:

1. In Power Query Editor, replace "24:00" with "00:00".

2. Change data type.

3. Close and Apply.

4. In Power BI Desktop relationship view, you can create relationships now.

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Icey Thank you, I really appreciate your help!