fillRightOrLeft Example | fill values right (or left) with optional fill columns and number of top rows.

fillRightOrLeft_Example.pq

let
    fillRightOrLeft = ( tbl as table, optional fillCols as list, optional topRows as number, optional fillLeft as logical) as table => 
    let
        fillFunction = if fillLeft ?? false then Table.FillUp else Table.FillDown,
        t = if topRows = null then tbl else Table.FirstN(tbl, topRows),
        allCols = Table.ColumnNames( tbl ),
        fillSideways = Table.TransformRows( t,
                each _ & Record.FromTable(
                    fillFunction( Record.ToTable( 
                        Record.SelectFields(_, fillCols ?? allCols, MissingField.Ignore)
                    ), {"Value"})
                )
            ),
        result = if topRows = null 
            then Value.ReplaceType( 
                    Table.FromRecords( fillSideways ), 
                    Value.Type(tbl)
                )
            else Value.ReplaceType( 
                    Table.FromRecords( fillSideways ) & Table.Skip( tbl, topRows ), 
                    Value.Type(tbl)
                )
    in
        result,
    Source = #table(
        type table [Entry = Text.Type, Category = Text.Type, SubCategory = Text.Type, Amount = Currency.Type], 
        {
            {"Expense", "Travel", null, 1500},
            {"Expense", null, "Airfare", 500},
            {"Expense", null, "Hotel", 1000},
            {"Expense", "Office Supplies", null, 300},
            {"Expense", null, "Stationery", 100},
            {"Expense", null, "Furniture", 200}
        }
    ),
    FillRight = fillRightOrLeft(Source, null, 3),
    FillRightCollSelection = fillRightOrLeft(Source, {"Category", "SubCategory"}, 3),
    FillLeft = fillRightOrLeft(Source, null, null, true),
    FillLeftCollSelection = fillRightOrLeft(Source, {"Category", "SubCategory"}, 3, true)
in
    FillLeftCollSelection