Forum Discussion

Codemunchkin's avatar
Codemunchkin
Frequent Visitor
4 years ago
Solved

Merging Table is Skipping Rows

Hi,

 

I am merging 2 tables on an index, once merge takes place the final table has missing/incorrect values in the first few rows. I am not sure why it's doing that. I have attached the file. 

 

This is the look up table:

 

This is the merged table before I expand the column:

The merge is happening on Index column(look up table) and Custom Index column in the other table.

 

Now after expanding the column I get this:

 

 

I am not sure what I am doing wrong here, any help would be appreciated. Thanks in advance! I have attached the excel file aswell!

 

https://docs.google.com/spreadsheets/d/1ZjFGxXDmSNK_Tkg4KRsbL6Hia2kwYtUJ/edit?usp=sharing&ouid=104392887111397866223&rtpof=true&sd=true

 

General Comment
Need Help
Show and Tell
Tutorial Requests
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    So I looked at your file and your "Do Not Touch - Custom Index for Data" query looks like it's almost what you're looking for. If you sort by [Index], then you do get ABC, CBA, BBB repeat.

     

    It's not skipping rows (check the row count), it's just not putting them in the order you expect.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Codemunchkin 

     

    How did you do the merge? And what are the expected results? Like this?

     

    Your Transpose table only contains 3 rows? Then merged on Index column?

    I have these two tables, you can paste in Advanced Editor via blank query

    Transpose

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCilKzCsuyC9OVYrViVZydHLWcXZy1HFyclKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Transpose", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Transpose", each Text.Split(_,",")}}),
    #"Expanded Transpose" = Table.ExpandListColumn(#"Trimmed Text", "Transpose"),
    #"Added Index" = Table.AddIndexColumn(#"Expanded Transpose", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

     Table

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCilKzCsuyC9OVYrViVZydHLWcXZy1HFyclKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Transpose", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Transpose", each Text.Split(_,",")}}),
    #"Expanded Transpose" = Table.ExpandListColumn(#"Trimmed Text", "Transpose"),
    #"Added Index" = Table.AddIndexColumn(#"Expanded Transpose", "Index", 1, 1, Int64.Type)
in
    #"Added Index"
    • Codemunchkin's avatar
      Codemunchkin
      Frequent Visitor
      4 years ago

      Hi,

       

      Both of your queries output the same table. I think you sent the same code twice.

       

      So I edited the question a bit, the merge is happening on Index column(look up table) and Custom Index column in the other table. The resultant table should be matching with custom index column and outputting the column Transpose.

       

      The merge was an inner merge.

       

      The custom index column is different from index column!

       

      You see custom index column goes from 1 to 3 and then repeats, the merge should be on those values, but when I expand the Tranpose column then values are not in the same order!

       

       

      This is what ends up happening, it should be ABC, CBA, BBB then repeat

       

       

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        4 years ago

        So I looked at your file and your "Do Not Touch - Custom Index for Data" query looks like it's almost what you're looking for. If you sort by [Index], then you do get ABC, CBA, BBB repeat.

         

        It's not skipping rows (check the row count), it's just not putting them in the order you expect.