cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
v-yulgu-msft Super Contributor
Super Contributor

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 443 members 4,338 guests
Please welcome our newest community members: