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

Expanded Table ,the index of the previous step of the data has changed

Hi Guys,

I only made an operation to extend the table, but the index of the previous step has changed. I don't know why this is the cause. Can you help me check it out?

 

first step

the second step

Just did the following:

steps

3 REPLIES 3
Community Support Team
Community Support Team

Re: Expanded Table ,the index of the previous step of the data has changed

Hi @jingyulong ,

 

When you click the expand option in the upper right side of your 1st page screenshot ,the following expanded page is a new table page totally, you can convert the last two steps to find differences, and the NewIndex maybe one of the differences.

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

jingyulong Frequent Visitor
Frequent Visitor

Re: Expanded Table ,the index of the previous step of the data has changed

@v-xicai 

这两步只有截图中的几行数据,都是提前过滤好,用来查找差异的。

 

如果我把第一步的数据重新建一个表来操作,没有任何问题。

 

其实我有疑问的是,两个步骤之间,就进行了一个扩展表的操作,对应关系按理来说不应该变,这里是不是有Bug,还是有其它好的办法来查找根本的原因。

 

Note:在这之前我有合并查询过,不知道有没有这个原因。重点是【下一步的操作,上一步的原始数据变了】

jingyulong Frequent Visitor
Frequent Visitor

Re: Expanded Table ,the index of the previous step of the data has changed

@v-xicai @v-xicai 

I don't know if this is a bug, I did some test data, Below is my repro step:

 


let 源 = Excel.CurrentWorkbook(){[Name="Team"]}[Content], #"Step 1 Change Type" = Table.TransformColumnTypes(源,{{"UserName", type text}, {"Score", Int64.Type}}), #"Step 2 Sort" = Table.Sort(#"Step 1 Change Type",{{"UserName", Order.Descending}, {"Score", Order.Descending}}), #"Step 3 Group By" = Table.Group(#"Step 2 Sort", {"UserName"}, {{"All Line", each Table.AddIndexColumn(_, "Rank", 1, 1), type table}}), #"Step 4 Expand “All Line”" = Table.ExpandTableColumn(#"Step 3 Group By", "All Line", {"Score", "Rank"}, {"Score", "Rank"}), #"Step 5 Meger Query" = Table.NestedJoin(#"Step 4 Expand “All Line”", {"UserName"}, Level, {"UserName"}, "Level", JoinKind.LeftOuter), #"Step 6 Expand ""Level""" = Table.ExpandTableColumn(#"Step 5 Meger Query", "Level", {"Level"}, {"Level.1"}) in #"Step 6 Expand ""Level"""

Step 2 Sort two column2.png

 

Step 3 I added an index in order to record rank 3.png

 

Step 4 Expand Table4.png

 

Step 5 Left join query, join the level table below5.png

 

5-1.png

 

Step 6 Expanded Table, the index of the previous step of the data has changed6.png

 

 

Since the index is added before, why is the order of the latter still changing? This will cause trouble for people. Later I found that if you add an index with a custom formula after sorting, the results displayed will be incorrect. If there is no sorting that starts first, the result of the last step is the same as the result of the step of indexing.

 

Expect: The result I expect is that what the eye sees is the desired result, and does not cause harm to others.

 

In addition: I don’t know how to attach test data, if you need I can send it by email.

 

 

 

 

Helpful resources

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.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 200 members 1,941 guests
Please welcome our newest community members: