Build Availability Table

DataNinja777  would it be possible to adjust this based on additional activity that is attached to training columns? Say for example "driving" training also have "First Aid" activity that is attached to it and may be scheduled always on Wednesdays from 11-12 so every time you counting we leave everything as is only this time we add 1 to Wedesday under column 11

Hi stribor45 ,

To integrate the "First Aid" schedule as part of the existing timetable, it’s best to handle this transformation in Power Query for consistency and efficiency.

1. Duplicate the Fact Table Query: Start by duplicating the main fact table query.
2. Filter for "Driving" Training: Apply a filter to include only rows where the training is "Driving."
3. Rename Training Field: Rename the "Training" field to "Driving (First Aid)" to clearly indicate that these entries include the "First Aid" component.
4. Adjust Start and End Times: Use the "Replace Values" feature in the Power Query ribbon to set the "Start Time" and "End Time" fields to reflect the "First Aid" schedule (e.g., Wednesdays from 11:00 to 12:00).

The final output will show "Driving (First Aid)" entries with the updated time slots, ensuring a seamless integration of both training and activity in your dataset.

I have attached an example pbix file for your reference.

Best regards,

DataNinja777   Thank you for sharing this. "driving" with its additional activity called "first aid" is an exampke  but other actiivties may or may not have additional activity scheduled at different day/time then the main activity.  Would this work as well on this solution?

I was trying to study the example you attached but i noticed that for participant #1 

#1 first aid on wednesday 11 to 12
driving on monday and ewednesday from 7-8 pm
swiming on monday and wednesday from 8:30 to 9:30 am

but the output starts half an hour earlier on  for first aid and driving

Hi stribor45 ,

Thank you for checking.  I tweaked the formula a little bit to fix the inaccuracy in output.  

Participants during Duration 30Min = 
VAR SelectedTime = MAX('TimeTable'[Time]) // Assume Time is in "HH:MM" format
VAR BucketStart = TIME(HOUR(SelectedTime), IF(MINUTE(SelectedTime) < 30, 0, 30), 0)
VAR BucketEnd = IF(MINUTE(SelectedTime) < 30, 
                   BucketStart + TIME(0, 30, 0), 
                   BucketStart + TIME(1, 0, 0))

// Concatenate participants within the time window
VAR ParticipantsList = 
    CONCATENATEX(
        FILTER(
            DISTINCT('Table'),
            'Table'[Start Time] <= BucketStart &&
            'Table'[End Time] > BucketStart
        ),
        RIGHT('Table'[Participant], 1),
        ", "
    )

// Return either the list of participants or '-' if no participants
RETURN
    IF(
        ISBLANK(ParticipantsList),
        "-", // Display "-" when no participants
        ParticipantsList
    )

Specifically changed part is as shown below where instead of BucketEnd, BucketStart variable was used.  

Using only BucketStart in the filter keeps the formula straightforward and avoids errors caused by unnecessary checks with BucketEnd. This approach reliably captures participants within each 30-minute interval as intended.

The output is displayed below, and a sample check has confirmed that it now shows the correct results.

I have attached the revised pbix file for your reference.

Best regards,