Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
abhig2023
New Member

Create rows for missing hours in Datetime column and fill desired value

Hello Experts,
I'm struggling with missing hours in Datetime column. 
My requirement:
1. Display hourly performance for each device in matrix format.  (My table (oracle) doesn't have the performance for some hours. For eaxmple: 1/8/2023 1:00 AM, 1/8/2023 4:00 AM, 1/8/2023 2:00 PM, 1/8/2023 7:00 PM are missing in table because the device powered off for some performance issues. So these hours are not pulled in table. I need to fill them with desired value )

          So, I tried to create a calendar table (start and end date from existing table) and tried joins (full outer/ inner join with calendar), but couldn't get the missing hour rows. Using calendar datetime as column in matrix, I was able to show 24 hours and all selected date range in table with blanks for non existing hours. But don't know how to fill the blank ones because they don't exist in table.
2. Display overall average performance of each device over date range selected.
          Device performance is calculated with 4 different KPI metrics (A1, A2, A3, A4) I need to calculate average performance. But if the value doesn't exist for misisng hour, it should write the following: (A1 = 0; A2 = 0, A3 = 100, A4 = 1). Only then i'll be able to get correct averages for all selected datetime. 

I tried several ways to pull the missing hours rows in table (as null - so I can fill something). I could get nowhere with this. Any suggestions would be much appreciated. 
Thanks
Abhig

1 REPLY 1
foodd
Super User
Super User

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.