Forum Discussion
Solved
Generating Missing Data
I am attempt to solve an issue where I believe I am starting with some misconceptions, so I would like to explain a very simple scenario, the solution and why the solution works. Image a Dimension t...
Hi JasonBurdetts,
Yes, this can be achieved either with DAX or Power Query, but based on your explanation, Power Query may be the simpler option. Since your actual Value column is an aggregate of integer entries that occur within each hour, the key is to aggregate first, then fill down.
The correct order would be:
- Aggregate the fact table by Hour
- Left join/merge it with the full Hour/Date dimension table
- Sort by Hour ascending
- Expand to get Value Column
- Fill down the aggregated value column. Note: First row would be null after applying Fill down, replace null with 'FALSE'.
💡 Helpful? Give a Kudos 👍 — keep the community growing
✅ Solved your issue? Mark as Solution ✔️ — help others find it faster
Best regards,
Rupasree Achari | BI & Fabric Analytics Engineer
Do this in Power Query, much cleaner than DAX here.
Start from your Hour list (1-12) and left join the Fact table on Hour. Missing hours show null.
Sort by Hour ascending.
Right-click the Value column, Fill > Down. That carries the last value forward into the blanks.
Replace any leftover null at Hour 1 with False.
If this answer helped, please click 👍 or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande