FillDown and Fillup with categories

Hi JasonBurdetts,

1. Merge Tables - Merge the Dimension (Category) table with the Fact table using a Left Join → This ensures all categories from the dimension table are retained.

2. Expand Fact Columns - Expand the merged table to include the required Value column from the fact table.

3. Sort Category - Sort the Category column in ascending order. This will group values like 1…12 Bar followed by 1…12 Foo

4. Fill Missing Values - You’ll notice null values where no fact data exists. Use Transform → Fill → Up (first) → Down (second) to populate those nulls correctly.

5. Final Sorting - Sort the Hour column in ascending order to get the expected output.

💡 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 

You can solve this in Power Query by creating all possible combinations of Hour × Category, merging with your fact table, and then applying Fill Down / Fill Up within each category.

Steps

1. Create a table containing all Hours (1–12)

2. Create a distinct Category table (Foo, Bar)

3. Cross join Hours and Categories

4. Merge with your fact table

5. Sort by Category and Hour

6. Group by Category and apply Fill Down + Fill Up on Value

Power Query approach

Assume your fact table is called Fact

Get distinct categories Categories = Table.Distinct(Table.SelectColumns(Fact, {"Category"}))

Create hours table Hours = Table.FromList({1..12}, Splitter.SplitByNothing(), {"Hour"})

Cross join

Add custom column in Categories: = Hours

Expand Hours to get:

Hour | Category
1    | Foo
2    | Foo
...
12   | Bar

Merge with fact table

Left join on:

• Hour

• Category

Expand Value column.

Fill values per category

Sort by:

• Category

• Hour

Then group by Category and apply:

Table.FillUp(
    Table.FillDown(_, {"Value"}),
    {"Value"}
)

This ensures missing rows inherit nearest previous/next known value within each category.

Expected output:

• Every Hour from 1–12 exists for Foo

• Every Hour from 1–12 exists for Bar

• Missing values are filled based on neighboring values

Helpful? A Kudos is always appreciated 👍
Solved your issue? Mark as Solution so others can benefit faster ✔️

Best regards,
Azad Singh Thakur
Senior BI Developer | Power BI | DAX | Fabric Analytics Engineer

Hi JasonBurdetts 

We wanted to follow up to check if you’ve had an opportunity to review the previous responses. If you require further assistance, please don’t hesitate to let us know

hi JasonBurdetts 
you solve the problem either in power query or in DAX. I am putting both the solution below.
1. Power Query

• Open Power Query Editor.
• Ensure you have your DimTable (containing all Hours 1-12 and Categories) and your sparse FactTable.

• Create a new Blank Query and open the Advanced Editor. Use the following M code pattern:

let
// 1. Merge the comprehensive Dimension table with the sparse Fact table
Source = Table.NestedJoin(DimTable, {"Category", "Hour"}, FactTable, {"Category", "Hour"}, "FactData", JoinKind.LeftOuter),

// 2. Expand the Value column (this will introduce nulls where the Fact table is sparse)
ExpandedFact = Table.ExpandTableColumn(Source, "FactData", {"Value"}, {"Value"}),

// 3. Group by Category so the Fill operations don't bleed across "Foo" and "Bar"
GroupedRows = Table.Group(ExpandedFact, {"Category"}, {
{"Data", each _, type table [Hour=nullable number, Category=nullable text, Value=nullable logical]}
}),

// 4. Sort each nested table by Hour, then Fill Down, then Fill Up
FillData = Table.TransformColumns(GroupedRows, {
{"Data", each Table.FillUp(Table.FillDown(Table.Sort(_, {{"Hour", Order.Ascending}}), {"Value"}), {"Value"})}
}),

// 5. Expand the transformed nested tables back out
ExpandedData = Table.ExpandTableColumn(FillData, "Data", {"Hour", "Value"}, {"Hour", "Value"}),

// 6. Sort the final output for presentation
FinalTable = Table.Sort(ExpandedData, {{"Hour", Order.Ascending}, {"Category", Order.Ascending}})
in
FinalTable

2. DAX

If you need to calculate this dynamically within the model, you can write a DAX Calculated Table.

FilledFactTable = 
GENERATE (
// Start with the complete Cartesian product of your Dimension
SUMMARIZE(DimTable, DimTable[Category], DimTable[Hour]),

VAR CurrentHour = DimTable[Hour]
VAR CurrentCategory = DimTable[Category]

// Attempt Fill Down: Find the latest available hour <= the current hour
VAR PrevHour = 
CALCULATE (
MAX ( FactTable[Hour] ),
FactTable[Category] = CurrentCategory,
FactTable[Hour] <= CurrentHour
)

// Attempt Fill Up: Find the earliest available hour > the current hour
VAR NextHour = 
CALCULATE (
MIN ( FactTable[Hour] ),
FactTable[Category] = CurrentCategory,
FactTable[Hour] > CurrentHour
)

// Coalesce: If there is no previous hour (PrevHour is BLANK), use the NextHour
VAR TargetHour = IF ( ISBLANK ( PrevHour ), NextHour, PrevHour )

// Retrieve the actual True/False value for the identified TargetHour
VAR ResultValue = 
CALCULATE (
SELECTEDVALUE ( FactTable[Value] ),
FactTable[Category] = CurrentCategory,
FactTable[Hour] = TargetHour
)

RETURN 
ROW ( "Value", ResultValue )
)

Please check this out. If this solves your problem, please mark this solution and gives a kudos. 

This is really a Power Query (M) problem rather than a DAX one, since you need every Hour x Category combination to physically exist as rows before FillDown/FillUp can do anything. Here's an approach:

1. Build a "scaffold" table containing every combination of Hour (1-12) and Category, e.g. via a cross join of your Category list and a list {1..12}.

2. Left-outer merge your sparse fact table onto that scaffold on Hour + Category, expanding the Value column (rows with no match will be null).

3. Sort by Category ascending, then Hour ascending.

4. Group by Category, and inside each group apply FillDown followed by FillUp on the Value column - doing both handles gaps at the very start and end of each category's range.

In M it looks like this:

let

    CategoryList = List.Distinct(Source[Category]),

    HourList = {1..12},

    Scaffold = Table.FromRecords(List.Combine(List.Transform(CategoryList, (cat) => List.Transform(HourList, (hr) => [Hour = hr, Category = cat])))),

    Merged = Table.NestedJoin(Scaffold, {"Hour","Category"}, Source, {"Hour","Category"}, "FactData", JoinKind.LeftOuter),

    Expanded = Table.ExpandTableColumn(Merged, "FactData", {"Value"}),

    Sorted = Table.Sort(Expanded, {{"Category", Order.Ascending},{"Hour", Order.Ascending}}),

    Grouped = Table.Group(Sorted, {"Category"}, {{"Rows", each Table.FillUp(Table.FillDown(_, {"Value"}), {"Value"}), type table}}),

    Result = Table.Combine(Grouped[Rows])

in

    Result

This gives you a fully populated table with all 12 hours for both Foo and Bar, each filled with the nearest known value in the correct direction, computed independently per category.

Hi JasonBurdetts​,
Please find the attached pbix file with a solution to your requirement.Please check and let me know if you have any more questions on this. Thanks in advance.
Best Regards,
Maruthi
LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/
X            -  Maruthi Siva Prasad - (@MaruthiSP) / X