Compile multiple sharepoint list into excel

Hi everyone,

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.

Thanks