add missing rows and add null value

[ELOO]

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 second column should the be null

1	x
2	y
3	z
5	a
6	b
8	c

Can You help me? 

thanks

ELOO

[AlienSx]

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

[PhilipTreacy]

Hi @ELOO 

Download example PBIX file

I started with this table

Using this code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUapQitWJVjICsirBLGMgqwrMMgWyEsEsMyArCcyyALKSlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t, Letter = _t]),
    NewTable = Table.FromList({List.Min(Source[Value])..List.Max(Source[Value])}, null, {"Value"}),
    #"Merged Queries" = Table.NestedJoin(NewTable, {"Value"}, Source, {"Value"}, "NewTable", JoinKind.LeftOuter),
    #"Expanded NewTable" = Table.ExpandTableColumn(#"Merged Queries", "NewTable", {"Letter"}, {"Letter"})
in
    #"Expanded NewTable"

How this works is that it takes the table in Source and creates a new table in the NewTable step (this is hand coded) that is a single column consisting of all values between the min and max from the Values column in the Source table

NewTable = Table.FromList({List.Min(Source[Value])..List.Max(Source[Value])}, null, {"Value"}),

You then do a table join on the on the Newtable and the Source table (again hand coded). 

#"Merged Queries" = Table.NestedJoin(NewTable, {"Value"}, Source, {"Value"}, "NewTable", JoinKind.LeftOuter),

You can then expand the column created by that join to give this

#"Expanded NewTable" = Table.ExpandTableColumn(#"Merged Queries", "NewTable", {"Letter"}, {"Letter"})

You'll need to do a bit of hand coding in the Advanced Editor to get this to work in your situation.  Happy to assist you if you post your full query here.

Regards

Phil

[ELOO]

Hi Phil, thanks for your reply, can you help me with the hand coding, I tried it myself but unsuccessfull thanks

ELOO