Merging queries in Power Query, but values change after hitting "Close and Apply".

[villasenorbritt]

I have a view I made in SQL that uses lag to get the difference between the current row and the row before. The difference is the downtime in seconds a machine is down. I am looking at source_id of 107, but for whatever reason, my company is using a source_id of 108 to determine if they are using new or old code because they want to compare the downtime for source 107 from the new and old code. 

I have the view I made which has both 107 and 108, but I filtered it in Power Query to only show 107.

I then queried that view once for source_id = 108 and data = 1 (new code) and once for source_id = 108 and data = 2 (old code).

These are in their own tables.

Next, I made a column from all three tables, my SorterDowntime view, Source108Data1, and Source108Data2 that is the date plus the StartofHour.

My idea is that I can use this  new column to see where the StartofHour + Date overlap, and used an inner join to "Merge queries". 

It looks perfectly fine in Power Query. Here are all my values for downtime in PowerQuery:

But when I hit "Close and Apply", this is what the dowtime now looks like:

Does anyone know what the issue is or how I can fix it? I also have access to SQL if there's an easier way to do it within there. Thanks!

[villasenorbritt · Accepted solution]

I ended up solving this in SQL. I did a self join and connected on two  custom columns, HourMinute and DateOnly. This seems to work fine since each source_id has data coming in every minute.  I can now bypass all the merging in Bi and the messed up downtime that happened after using Power Query. 

[amitchandak]

@villasenorbritt , seem like date is coming as number, change datatype to datetime

[villasenorbritt]

I'm working with downtime which is not really a "time", it is an integer that represents the seconds the machine was down. I use a duration measure to fix the formatting in my actual visuals. And the column to the left there is data, not date. I checked all my datatypes and all the dates/time columns are marked correctly. 

Any other ideas?