Forum Discussion

villasenorbritt's avatar
villasenorbritt
Resolver I
3 years ago
Solved

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

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!

 

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

3 Replies

    • villasenorbritt's avatar
      villasenorbritt
      Resolver I

      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?

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