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

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

Can You help me? 

thanks

ELOO

 

Need Help
Show and Tell

3 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    3 years ago

    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's avatar
      ELOO
      Frequent Visitor
      2 years ago

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

      ELOO

  • 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