Create rows for missing weekly data

[PH573]

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 Name	App Code	Start Day	Start Time	End Day	End Time
Generic	11111	Mon	9:00:00 AM	Fri	5:00:00 PM

becomes this:

App Name	App Code	Day	Start Time	End Time
Generic	11111	Mon	9:00:00 AM	11:59:59 PM
Generic	11111	Tue	12:00:00 AM	11:59:59 PM
Generic	11111	Wed	12:00:00 AM	11:59:59 PM
Generic	11111	Thu	12:00:00 AM	11:59:59 PM
Generic	11111	Fri	12:00:00 AM	5: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.

[Ashish_Mathur]

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?

[hnguy71 · Accepted solution]

@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:

Attached is a sample pbix

[PH573]

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?

[hnguy71]

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.