Unable to use Power Query to Connect to a Sharepoint List

%3CLINGO-SUB%20id%3D%22lingo-sub-96695%22%20slang%3D%22en-US%22%3EUnable%20to%20use%20Power%20Query%20to%20Connect%20to%20a%20Sharepoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-96695%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20import%20a%20SharePoint%20list%20in%20via%20PowerQuery.%20%26nbsp%3BWhen%20I%20try%20to%20use%20the%20root%20folder%20(and%20any%20variation%20there%20of)%20with%20my%20windows%20credentials%20I%20get%20an%20error%3A%20400%2C%20Bad%20Request.%20%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EI've%20tried%20using%20my%20Organizational%20account%20(like%20when%20you%20login%20the%20first%20time%20and%20use%20your%20email)%20it%20says%20%22we%20are%20unable%20to%20connect%20because%20this%20creditial%20type%20isn't%20supported%20for%20this%20resource.%20%26nbsp%3BPlease%20choose%20another%20credential%20type.%22%20%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EI'm%20at%20a%20loss%20as%20to%20what%20to%20do%20next...%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%2C%20Power%20BI%2C%20Get%20%26amp%3B%20Transform%2C%20Power%20Query%20SharePoint%20Lists%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-96695%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EGet%20%26amp%3B%20Transform%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20Query%20SharePoint%20Lists%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-96984%22%20slang%3D%22en-US%22%3ERe%3A%20Unable%20to%20use%20Power%20Query%20to%20Connect%20to%20a%20Sharepoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-96984%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20David%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAm%20i%20correct%20that's%20on-premises%20Sharepoint%3F%20For%20my%20knowledge%20SPO%20URL%20structure%20looks%20like%26nbsp%3B%3CYOURDOMAIN%3E.sharepoint.com%2Fsites%20(or%20%2Fteams%2C%20or%20%2Fsearch).%20Or%20for%20HP%20that's%20customized%20structure%3F%3C%2FYOURDOMAIN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyway%2C%20for%20SPO%20list%20Organizational%20(Microsoft)%20account%20shall%20work%2C%20same%20with%20which%20you%20login%20to%20O365.%3C%2FP%3E%3CP%3EFor%20Sharepoint%20on-premises%20works%20Windows%20account%20as%20in%20Active%20Directory.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20your%20root%20URL%20you%20use%20to%20connect%20is%20to%20be%20...%2Fteams%2FTSPO%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20menu%20there%20are%20two%20connectors%2C%20on%20Sharepoint%20Online%20List%20and%20on%20Sharepoint%20List%2C%20but%20actually%20both%26nbsp%3Bshall%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1372002%22%20slang%3D%22en-US%22%3ERe%3A%20Unable%20to%20use%20Power%20Query%20to%20Connect%20to%20a%20Sharepoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1372002%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Sergey%2C%3C%2FP%3E%3CP%3EI%20have%20a%20similar%20question%3A%20connecting%20to%20the%20site%20I've%20selected%20anonymous%20access.%3C%2FP%3E%3CP%3EI%20do%20not%20see%20how%20I%20can%20change%20this%20option.%20Excel%20remembers%20the%201st%20setting%20and%20does%20not%20ask%20for%20the%20type%20of%20credentials.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20Evgeny%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1372955%22%20slang%3D%22en-US%22%3ERe%3A%20Unable%20to%20use%20Power%20Query%20to%20Connect%20to%20a%20Sharepoint%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1372955%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F660073%22%20target%3D%22_blank%22%3E%40Evgeny_Kazakov%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEvgeny%2C%3C%2FP%3E%0A%3CP%3EData-%26gt%3BGet%20Data-%26gt%3BData%20Source%20settings%3C%2FP%3E%0A%3CP%3EHere%20select%20your%20source%20and%20edit%20permissions%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20510px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F190012i6579CEF8F36E3ABE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I'm trying to import a SharePoint list in via PowerQuery.  When I try to use the root folder (and any variation there of) with my windows credentials I get an error: 400, Bad Request.  

I've tried using my Organizational account (like when you login the first time and use your email) it says "we are unable to connect because this creditial type isn't supported for this resource.  Please choose another credential type."  

I'm at a loss as to what to do next... 

 

Excel, Power BI, Get & Transform, Power Query SharePoint Lists

3 Replies
Highlighted

Hi David,

 

Am i correct that's on-premises Sharepoint? For my knowledge SPO URL structure looks like <yourdomain>.sharepoint.com/sites (or /teams, or /search). Or for HP that's customized structure?

 

Anyway, for SPO list Organizational (Microsoft) account shall work, same with which you login to O365.

For Sharepoint on-premises works Windows account as in Active Directory.

 

And your root URL you use to connect is to be .../teams/TSPO

 

In menu there are two connectors, on Sharepoint Online List and on Sharepoint List, but actually both shall work.

Highlighted

@Sergei Baklan 

Hi Sergey,

I have a similar question: connecting to the site I've selected anonymous access.

I do not see how I can change this option. Excel remembers the 1st setting and does not ask for the type of credentials.

 

Thanks, Evgeny

Highlighted

@Evgeny_Kazakov 

Evgeny,

Data->Get Data->Data Source settings

Here select your source and edit permissions

image.png