Hi all,
I am in need of some help please.
We have a table where we log PC 1 and PC 2 operational status.
We have Columns: PC Name (PC01, PC02), status (Available, Unavailable or Working), Start Time (Date/Time) and End Time (Date/Time).
I need to find the 'data gaps' where the Start Time of a new status does not equal the End Time of the last status of that PC,
then add a new row to the table showing the 'data gap' period as status 'Unavailable'.
The above is the table we currently have and highlighted in red is the 'data gap'.
The above is what i need to achieve, an added row with the 'data gap' showing in status 'Unavailable' as highlighted in green.
Any help is very much appreciated 🙂
Thanks 🙂
Thanks for Posting in Microsoft forum Community.
As per the Query you mentioned for Solution required for finding 'data gap' in table and adding a new row showing the correct status. The provided DAX query is designed to find gaps in a table's data where the StartTime of a new row does not immediately follow the EndTime of the previous row for the same PC Name. It then creates new rows to fill those gaps, assigning them the status "Ideal". Here's a breakdown of how the code works:
Step1: First it Creates a new intermediate table (GapRows) that includes additional calculated columns:
VAR GapRows =
ADDCOLUMNS(
'Data', // Table name is 'Data'
"NextStartTime",
CALCULATE(
MIN('Data'[StartTime]), // Get the next StartTime for the same PC Name
FILTER(
'Data', // Filtering to get the next row
'Data'[PC Name] = EARLIER('Data'[PC Name]) &&
'Data'[StartTime] > EARLIER('Data'[StartTime]) // Ensure we only look ahead
)
),
"CurrentEndTime", 'Data'[EndTime] // Rename existing EndTime to avoid conflict
)
Step2: Identifies rows where there is a gap between the CurrentEndTime of a row and the NextStartTime of the next row for the same PC Name:
VAR IdealRows =
SELECTCOLUMNS(
FILTER(
GapRows,
NOT ISBLANK([NextStartTime]) && [CurrentEndTime] < [NextStartTime] // Detect gaps
),
"PC Name", [PC Name],
"Status", "Ideal", // Add status as "Ideal" for the gap rows
"StartTime", [CurrentEndTime], // Start time of the "Ideal" row is the current EndTime
"EndTime", [NextStartTime] // End time of the "Ideal" row is the next StartTime
)
Step3: Combine Original and Gap Rows:
RETURN
UNION(
'Data', // Combine original data
IdealRows // Combine with the newly generated Ideal rows
)
Using the above code, I obtained the expected output. I opted to use "Ideal" instead of "Unavailable" to enhance clarity and understanding.
By utilizing conditional formatting, I was able to highlight the desired results.
Using the Background Color feature, I set a rule to get the desired results.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
Thanks for Posting in Microsoft forum Community.
As per the Query you mentioned for Solution required for finding 'data gap' in table and adding a new row showing the correct status. The provided DAX query is designed to find gaps in a table's data where the StartTime of a new row does not immediately follow the EndTime of the previous row for the same PC Name. It then creates new rows to fill those gaps, assigning them the status "Ideal". Here's a breakdown of how the code works:
Step1: First it Creates a new intermediate table (GapRows) that includes additional calculated columns:
VAR GapRows =
ADDCOLUMNS(
'Data', // Table name is 'Data'
"NextStartTime",
CALCULATE(
MIN('Data'[StartTime]), // Get the next StartTime for the same PC Name
FILTER(
'Data', // Filtering to get the next row
'Data'[PC Name] = EARLIER('Data'[PC Name]) &&
'Data'[StartTime] > EARLIER('Data'[StartTime]) // Ensure we only look ahead
)
),
"CurrentEndTime", 'Data'[EndTime] // Rename existing EndTime to avoid conflict
)
Step2: Identifies rows where there is a gap between the CurrentEndTime of a row and the NextStartTime of the next row for the same PC Name:
VAR IdealRows =
SELECTCOLUMNS(
FILTER(
GapRows,
NOT ISBLANK([NextStartTime]) && [CurrentEndTime] < [NextStartTime] // Detect gaps
),
"PC Name", [PC Name],
"Status", "Ideal", // Add status as "Ideal" for the gap rows
"StartTime", [CurrentEndTime], // Start time of the "Ideal" row is the current EndTime
"EndTime", [NextStartTime] // End time of the "Ideal" row is the next StartTime
)
Step3: Combine Original and Gap Rows:
RETURN
UNION(
'Data', // Combine original data
IdealRows // Combine with the newly generated Ideal rows
)
Using the above code, I obtained the expected output. I opted to use "Ideal" instead of "Unavailable" to enhance clarity and understanding.
By utilizing conditional formatting, I was able to highlight the desired results.
Using the Background Color feature, I set a rule to get the desired results.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
I had a look at your sample data but there isnt any row where previous endtime <> current start time other than the first row per pc
Firs you need to identify which rows in your data has a gap by accessing the data from the immediately preceding row based on a datetime column
Another table is creating filtering the gap rows and assigning a different value to Status and EndTime. It is then combined with the original table without the extra columns.
Please note that when using UNION, the columns must match in both number and order at the time of creation. Altering the column order after the calculated table has been created will lead to incorrect results. In such cases, the table must be deleted and recreated to ensure accuracy.
This could have been done using Power Query but will involve a lot of merging and can be slow with large datasets.
Helpful resources
Power BI Monthly Update - August 2025
Check out the August 2025 Power BI update to learn about new features.
Fabric Community Update - August 2025
Find out what's new and trending in the Fabric community.
