I have a internal company sharepoint (I think it's on SP 2013) with multiple lists. My goal is to compile all the lists into an excel table that can be updated whenever the sharepoint list details changed and share the excel table with my colleagues that have lesser rights to the sharepoint site than me. (e.g. "uploaded" column is checked and the table returns a true value).
I am trying to use powerquery to query all the lists into an excel file but I ran into authentication issues. Below are the steps that I have taken.
Data tab > Get Data > From other sources > Sharepoint list
Authentication methods tried & error messages.
Microsoft account : we were unable to connect because this credential type isn't supported for this resource. Please choose another credential type.
Windows : Use my current credentials Error : Details: "OData: The given URL neither points to an OData service or a feed
Any suggestions much appreciated. I am considering to download multiple owssvr files for all the lists to link into the excel file but i'm unsure if it will work.