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
Microsoft v-yulgu-msft
Microsoft

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?

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors