Merging Table is Skipping Rows

[Codemunchkin]

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=10439...

[Anonymous]

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]

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 · Accepted solution]

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.

[Anonymous]

Hi @Codemunchkin 

I think @AlexisOlson  has answered your question, it did not skip your rows, just simply did not appear in the order of your original Index, you can sort it. I use Index column to sort all the time as I can't control the order