Forum Discussion
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 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.
Anonymous , 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
4 Replies
hnguy71Super User
Anonymous , 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
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 Anonymous,
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.
- 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?
