cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PH573
Regular Visitor

Create rows for missing weekly data

Hello,

 

A while back, I had a question regarding day of week and time data (app availability). I have since transformed this data using a coworker's SQL script, but have now run into a new issue.

 

The script I am using takes data from an enterprise data warehouse which expresses the time an app is online on a given day(s) of the week and expresses it in a format that splits it out by day of the week. For instance, this:

App NameApp CodeStart DayStart TimeEnd DayEnd Time
Generic11111Mon9:00:00 AMFri5:00:00 PM

 

becomes this:

App NameApp CodeDayStart TimeEnd Time
Generic11111Mon9:00:00 AM11:59:59 PM
Generic11111Tue12:00:00 AM11:59:59 PM
Generic11111Wed12:00:00 AM11:59:59 PM
Generic11111Thu12:00:00 AM11:59:59 PM
Generic11111Fri12:00:00 AM5:00:00 PM

 

The problem here is that the data warehouse does not list days in which an app is not online, and the script has no logic built into it to account for this - it would not create rows for Sunday or Saturday. This leads to gaps when attempting to visualize data, in particular with time series.

Is there a query I could run that would create the missing rows, or would this need to be created manually somehow? Thank you in advance for any assistance.

1 ACCEPTED SOLUTION
hnguy71
Super User
Super User

@PH573 , this can be achieved in multiple ways but I'll try it in PowerQuery.

 

I believe this is the result you're looking for:

hnguy71_0-1666658828659.png

 

Attached is a sample pbix



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

If you had a Date column in your dataset, this would have been a simple one to solve.  Do you have a Date column?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
hnguy71
Super User
Super User

@PH573 , this can be achieved in multiple ways but I'll try it in PowerQuery.

 

I believe this is the result you're looking for:

hnguy71_0-1666658828659.png

 

Attached is a sample pbix



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

This is indeed the end result I'm looking for, but I'm running into a problem following the steps laid out in your sample PBIX file. When attempting to join the warehouse data to the final table (using the warehouse data in place of your sample data), I get all null values in the merged columns and I am not sure what I am doing wrong. I've made sure to match the merged data types to what's in the warehouse data, including with the merge key on both tables. Is there something else I'm missing?

Hi @PH573,

 

The unique identifier might not be strong enough and thus returning black results? Try to go the other way around by changing the sample data with a few of your real data and see if you get the right results. 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.