Log In
Register
Home
March 3, 2021
Offline
Hi all, I am having trouble with one of my Power Queries, which gets data from one of our organisation's SharePoint Online Lists. While it was working well it has started having issues with credentials. The problem is that I am getting seemingly endless "We couldn't authenticate with the credential provided. Please try again" towards the end of the queries execution. As if I wasn't already signed in a window pops up on the Anonymous tab, so I change this to the Microsoft Account tab and click [Connect]. It re-signs in fine, and the query starts to load again, only to ask for credentials again before it finishes.
I have checked the check the Data source settings and made sure the correct tab is selected and saved, it says Credentials Type: Organisational account and Privacy Level: Organisational. I've also checked the Query Options and tried "Ignore the Privacy Levels" and turning off Background Data load with no effect. It's as though I successfully sign in to the Microsoft Account but before the query finishes, the Sign In details are lost.
As a bit of context, in my case the raw SharePoint Online List is around 51,000 rows and I needed to see the data is several different representations in my workbook. I therefore created a "base query" that extracts all the rows from SP and also does all the cleansing and adjustments needed like renaming columns to match the display names and converting the UTC dates to local. This query is retained as Connection Only and not loaded into my workbook. I then created a separate set of scenario queries that reference the base query and applying the filtering I need for each.
What's interesting is that the smaller scenarios seem to load fine but where more than 5,000 rows are being returned it has these credentials issues. By the way I can export the data from within SharePoint without issue, but the resulting file is just too big for me to incorporate that worksheet into my spreadsheets.
Anyone seen or heard of this problem?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Chris,
Can you try again but with another browser set as default? If Edge is your default browser, change to Chrome or any other.
March 3, 2021
Offline
Hi Catalin, thanks for the suggestion. I did as you suggested and changed the default browser to Chrome, tested it to make sure Chrome opened when I clicked on a link, and ran the PQ again. The results were the same.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
That was my only suggestion unfortunately, never hit this problem
March 3, 2021
Offline
Hi Catalin, I think I found the problem/solution. In my query I have a couple of ExpandRecordColumn statements to extract the user names contained within the Created By and Modified By columns. When I removed these steps the query ran without issue. After a bit of googling it appears that these statements are ok with nulls but do not like "" as a value. And as these were only included in my dataset as nice-to-haves, I could just leave them out without issue. Why this triggered a credentials error I can't say. Thanks for your help and suggestions though.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Thank you for feedback Chris, nice to hear you managed to find a way out.
1 Guest(s)
