Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
normang
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
Employee
Employee

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.

Hi, any solution?

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.