Issue with the group ranking in Power Query (April...

JoK · ‎04-17-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.

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!

v-junyant-msft

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

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

JoK

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?

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

Many thanks, Jo

v-junyant-msft

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