Creating relationship on date/time column with different format

[Anonymous]

Hi, I have 2 queries and I'd like to create a relationship between them on the date/time column. Each query is one row per hour and includes all of the days in 2019 (24 rows per day).

My goal is to bring hourly data together from seperate queries. 

- 1st query date/time column is labeled [HourEnding] and is of type "text"  (it won't let me convert this column to a "date/time" type. Each 24th hour shows correctly in Power BI, but shows up as "43467" when I export to excel. See below)

- 2nd query date/time column is labeled [Delivery Date and Time Normalized] and is of type "date/time"] 

Here are samples from each query. I tried left and right functions to concatenate whole numbers but struggled with the leading zeros. I'm sure there is a better method. Thanks in advance! 

HourEnding

1/1/2019 1:00

1/1/2019 2:00

1/1/2019 3:00

1/1/2019 4:00

1/1/2019 5:00

1/1/2019 6:00

1/1/2019 7:00

1/1/2019 8:00

1/1/2019 9:00

1/1/2019 10:00

1/1/2019 11:00

1/1/2019 12:00

1/1/2019 13:00

1/1/2019 14:00

1/1/2019 15:00

1/1/2019 16:00

1/1/2019 17:00

1/1/2019 18:00

1/1/2019 19:00

1/1/2019 20:00

1/1/2019 21:00

1/1/2019 22:00

1/1/2019 23:00

43467

1/2/2019 1:00

1/2/2019 2:00

1/2/2019 3:00

1/2/2019 4:00

1/2/2019 5:00

1/2/2019 6:00

1/2/2019 7:00

1/2/2019 8:00

1/2/2019 9:00

1/2/2019 10:00

1/2/2019 11:00

1/2/2019 12:00

1/2/2019 13:00

1/2/2019 14:00

1/2/2019 15:00

1/2/2019 16:00

1/2/2019 17:00

1/2/2019 18:00

1/2/2019 19:00

1/2/2019 20:00

1/2/2019 21:00

1/2/2019 22:00

1/2/2019 23:00

43468

Delivery Date and Time Normalized

1/1/2019 0:00

1/1/2019 1:00

1/1/2019 2:00

1/1/2019 3:00

1/1/2019 4:00

1/1/2019 5:00

1/1/2019 6:00

1/1/2019 7:00

1/1/2019 8:00

1/1/2019 9:00

1/1/2019 10:00

1/1/2019 11:00

1/1/2019 12:00

1/1/2019 13:00

1/1/2019 14:00

1/1/2019 15:00

1/1/2019 16:00

1/1/2019 17:00

1/1/2019 18:00

1/1/2019 19:00

1/1/2019 20:00

1/1/2019 21:00

1/1/2019 22:00

1/1/2019 23:00

1/2/2019 0:00

1/2/2019 1:00

1/2/2019 2:00

1/2/2019 3:00

1/2/2019 4:00

1/2/2019 5:00

1/2/2019 6:00

1/2/2019 7:00

1/2/2019 8:00

1/2/2019 9:00

1/2/2019 10:00

1/2/2019 11:00

1/2/2019 12:00

1/2/2019 13:00

1/2/2019 14:00

1/2/2019 15:00

1/2/2019 16:00

1/2/2019 17:00

1/2/2019 18:00

1/2/2019 19:00

1/2/2019 20:00

1/2/2019 21:00

1/2/2019 22:00

1/2/2019 23:00

[edhans]

I'm not quite understanding the problem. When I put your data into Power BI, it works fine. I don't know what you mean you cannot convert it to DateTime from Text. Power Query will most certianly do this.

Your data as text:

Then when I convert to Date/Time

See this M code to see how it works.

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdG7CcMAEAXBVoxjg3Trr9yKUP9tOBWM0xccy82+X2eZpXW2y3zX9XrcTkssd5YHy5PlxfJm+bBsLLM6WT1mj91j+Fg+po/tY/xYn/X9+bn1WX86FXqhF3qhF3qhF3qhF3qpl3qpl3qpl3qpl3qpl3qpl3qpl3qd9Y4f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [HourEnding = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"HourEnding", type datetime}})
in
    #"Changed Type"

If you are still having issues, can you provide your actual sample data file? See links below to assist in providing source data.

If you are trying to convert this in Power BI on the DAX side, that is the issue. It has to be done in Power Query. Click the Transform Data button on the Home ribbon, then next to the column there is a little ABC icon denoting it is text. Click that and change it to Date/Time.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Anonymous]

@edhans 

Thank you for the quick and detailed response, I really appreciate your help.

I should have mentioned, I only included a small sample from my dataset, the error must be in a row that I did not include. I followed your steps but still generated an error. I've included a link to the Power BI file below if you don't mind taking a look. 

As I mentioned, I'm trying to create a relationship on time so that I can bring 'Native Load Report'[EAST] into the 'DART 2019 with correct date' table. 

Thanks again! 

https://www.dropbox.com/s/4mtham8qzih59q1/Jsp_1.pbix?dl=0

[edhans]

your file has links to outside tables, so when I get into Power Query, I just get errors that I don't have access to the data.

If you could also provide a complete image of what your expected result is. Mock something up in Excel.

[Anonymous]

@edhans I'm sorry for the late repsonse and I'm sorry I didn't include the excel file. Rather than sharing the Power BI file, I've included the two excel files that feed my Power BI. Both excel files are 1 row per hour for all of 2019.

I'm simply trying to bring column C from "Native Load 2019" into the other excel file "DART 2019 with correct Date_Example" so that the correct values align with the correct date/time. As mentioned, I'm struggling to create an ID on the date/time columsn from both files. 

Thanks again!

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...