Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
@villasenorbritt , seem like date is coming as number, change datatype to datetime
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |