cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
normang Frequent Visitor
Frequent Visitor

Expanding table results in exchanged values of original column

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

Capture1.PNG

 

 

after expansion, index changed from 1 to 2.

Capture2.PNG

 

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.

3 REPLIES 3
Highlighted
Community Support Team
Community Support Team

Re: Expanding table results in exchanged values of original column

Hi @normang ,

 

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

1.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
normang Frequent Visitor
Frequent Visitor

Re: Expanding table results in exchanged values of original column

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.

normang Frequent Visitor
Frequent Visitor

Re: Expanding table results in exchanged values of original column

Hi, any solution?