Expanding table results in exchanged values of original column

[normang]

Hi, all,

I encountered a really weird result. I joined two tables on personid, before expanding index for rid 348 is 1, index of rid 266 in merged table is 1

after expansion, index changed from 1 to 2.

Any idea on where this error comes from?

Solution is sorting the rid column before expanding the table, but that is not supposed to do I think.

[v-yulgu-msft]

Hi @normang ,

I was not able to reproduce this behavior on my side.

Did you apply any other change? Was "Index" added in Power Query or original column in source table?

Please remove queries and re-load them, merge tables again to test if problem persists.

Regards,

Yuliana Gu

[normang]

Hi, any solution?

[normang]

Hi, Yuliana, @v-yulgu-msft 

I have created a sample which can recreate this issue.

Table1:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlLSUTJRitWBsIzBrEQgywjOMlSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, category = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"category", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Name", Order.Ascending}, {"category", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Name"}, {{"Count", each Table.AddIndexColumn(_,"index",1,1), type table}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"category", "index"}, {"category", "index"})
in
#"Expanded Count"

Table2:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlLSUTI0MFCK1YGxDcHsRDDbCIltrBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, seq = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"seq", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"name"}, {{"Count", each Table.AddIndexColumn(_,"index",1,1), type table}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"seq", "index"}, {"seq", "index"})
in
#"Expanded Count"

Merge1:

let
Source = Table.NestedJoin(Table1,{"Name"},Table2,{"name"},"Table2",JoinKind.Inner),
#"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"seq", "index"}, {"seq", "index.1"})
in
#"Expanded Table2"

Then look at merge1 source and #"Expanded Table2" you will find exact behavior as I mentioned. I think powerbi regenerates the index column when I expand the table after merge based on data's original order.