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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
JoK
New Member

Issue with the group ranking in Power Query (April 2024)

Hi Power BI users, I have a problem with Power BI transformations (Power Pivot) that affects the connectivity to SharePoint. It worked in previous version and after recently upgrading to April 2024 I have noticed an issue. This unfortunately affects one of the ways we do ranking in Power BI - especially the old dashboards that used it before Microsoft finally improved the RANKX DAX function or some complex processes to get data in the right shape. The Power Query method is using grouping and indexing through custom column and worked perfectly in the past. However, you can find now that you are unable to expand data due to authorisation problem. 

 

JoK_1-1713348150067.png

 

JoK_2-1713348263334.png

 

What I am trying to do in transformation is:

0. Load the data from Sharepoint (successful)

1. Sort data desc on the date field (successful)

2. Group by ID (Count, All Rows) (successful)

3. Add custom colum Custom = Table.AddIndexColumn([Count], "Rank", 1, 1) (successful)

4. Expand the tranformed data  (fails)

 

The normal authorisation method that was used previously successfuly no longer works so i cannot authorise after this step. So I cannot get the data loaded.

 

I do not undertstand why when I try to expand the data suddently it tries to authorise again but it fails despite me being logged in and having connection to data. If I go to any of previous steps, everything starts to work again as expected and authorisation is no longer a problem. 

 

I cannot understand why this is happening and how to fix it. I know I can use RANKX in dax to achieve ranking but for some datasets we still require it done as a part of transformations. 

 

Did anybody seen this issue or is aware of the possible cause ot fix?

 

Thanks in advance!

 

1 ACCEPTED SOLUTION
v-junyant-msft
Community Support
Community Support

Hi @JoK ,

After testing, I have successfully reproduced and confirmed the issue. it looks like the previous known issue was not fixed.
I would recommend that you start with SharePoint Online connector implementation 2.0 or roll back your version of Power BI Desktop to the February version (as this issue occurred with both the March and April versions). I'll keep you posted as soon as this issue has any updates.

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

View solution in original post

9 REPLIES 9
v-junyant-msft
Community Support
Community Support

Hi @JoK ,

After testing, I have successfully reproduced and confirmed the issue. it looks like the previous known issue was not fixed.
I would recommend that you start with SharePoint Online connector implementation 2.0 or roll back your version of Power BI Desktop to the February version (as this issue occurred with both the March and April versions). I'll keep you posted as soon as this issue has any updates.

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

v-junyant-msft
Community Support
Community Support

Hi @JoK ,

Have you solved your problem?
If not, can you tell me whether your dataset is the Sharepoint (Online) List or the Sharepoint Folder? And whether you are using SharePoint Online connector implementation 1.0?
We had a known issue similar to what you are facing, but that issue occurred in March. Could you please provide me with more detailed information so that I can test it further?

Best Regards,
Dino Tao 

Hi Dino, 

Thank you for your response. Yes, we still have this issue. 

I am connecting to Sharepoint (online) list. The connector implementation is 1.0 as that was the one that worked in the past. Do you think it s worth trying 2.0?

JoK_0-1713951257554.png

The Power BI version that I am currently running is: 2.128.952.0 64-bit (April 2024)

 

Many thanks, Jo

Hi @JoK ,

To make it easier for our engineers to solve the problem, I need to gather some information.
First, roughly when did this problem start happening?
Second, why are you using SharePoint Online connector implementation 1.0 instead of SharePoint Online connector implementation 2.0?

Thank you for your cooperation!

Best Regards,
Dino Tao

Apologies for late reply and thank you for looking into it! We use the older connector 1.0 as the issue was noticed once redeveloping an older dashboard (we have a very large number of already existing reports). We only discovered it few days before I posted - I guess would be related to updating to more recent Power Bi version. I have checked and updating connector to 2.0 version fixed the issue for us.  Thank you!

How many lookup columns in your list?

Hi, there is no lookup columns used in that list.

have you raised the ticket?

lbendlin
Super User
Super User

If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors