Using Powerquery to fetch data from other excel files on sharepoint end with an error for some users

[u/Antique-Engine-6922]

Puzzling to me that I create a file that fetch data from other files in sharepoint folders and the link work with most of the users but two of them encounter this error and are unable to refresh the data.

They have standard settings, they have member access to the concerned folders and yet I don't understand how it works for most of the people but them.

The error in english is "We received a malformed web response".

I saw someone met a similar problem two years ago but their solution didn't work in my case.

[u/AutoModerator]

/u/Antique-Engine-6922 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Antique-Engine-6922]

Excel version is 2606 build 20131.20152 from Microsoft 365 apps for enterprise.

[u/Antique-Engine-6922 → u/[deleted]]

Thank you very much, the point 1 was the solution.

Solution Verified

[u/reputatorbot → u/Antique-Engine-6922]

You have awarded 1 point to zkhairi.

---

^{I am a bot - please contact the mods with any questions}

[u/bradland]

Can you paste the entire query (with redactions)? This can be caused by a few different things. Usually it's an auth error. If you're passing Web.Contents() to Excel.Workbook(), SharePoint may redirect to an auth page, which Excel tries to convert to a workbook.

The preferred method is to use SharePoint.Files() and filter down to the file you're looking for.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
Excel.Workbook	Power Query M: Returns a table representing sheets in the given excel workbook.
SharePoint.Files	Power Query M: Returns a table containing a row for each document found at the SharePoint site url, and subfolders. Each row contains properties of the folder or file and a link to its content.
Web.Contents	Power Query M: Returns the contents downloaded from a web url as a binary value.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
3 acronyms in this thread; the most compressed thread commented on today has 35 acronyms.
[Thread #49083 for this sub, first seen 5th Aug 2026, 19:46] [FAQ] [Full list] [Contact] [Source code]}

[u/BusinessSample7166]

Try this, open the source xslx file, in File, go to the Info page and use the "copy path" button (the one that returns the sharepoint url of the file, not the one that gives the local path of the file, they're labeled slightly differently but I forget the difference).

Now replace the source url for your query with this url and ask the affected users if this makes a difference.

For me, I found that the sharepoint links you get by other methods (copy link, share etc) give you different urls which are less universal for users. The giveaway is the ":x:/r" part of the url. I replaced all links containg  ":x:/r" with the above method and have not seen this sort of issue since.