Forum Discussion

ELOO's avatar
ELOO
Frequent Visitor
3 years ago

add missing rows and add null value

Hi I have a table with two columns,  the firs column are consecutive numbers, however some numbers are missing.  I want to add in power query editor row for all missing number, the value in the sec...
Need Help
Show and Tell
AlienSx's avatar
AlienSx
Super User
3 years ago

Hi, ELOO 

// suppose you have a table "your_table" with Column1 and Column2
let
    Source = your_table,
    table_numbers = Source[Column1],
    all_numbers = List.Buffer({List.Min(table_numbers)..List.Max(table_numbers)}),
    diff = List.Buffer(List.Difference(all_numbers, table_numbers)),
    out = 
        Table.FromRows(
            List.Combine(
                {Table.ToRows(Source), 
                List.Transform(diff, (x) => {x, null})}
            )
        )
in
    out