cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VFXPro1
Level 8

Load data from external data sources using Power Query - constant credentials failures...

I have a really large (>1M rows) 70 MB CSV file that cannot be open in Excel due to it's size.

I'm trying to use new CDS Get Data feature to import via Power Query.  The file is stored in my OneDrive for Business.

 

No matter what I tried (anonymous or corporate account), I keep getting credential error.

 

Any tip? Is the file too big?  Perhaps, it doesn't work with brand new CDS for App entities yet?

 

Is the path problematic? It's a copied link from the "share" menu

https://superleaguegaming-my.sharepoint.com/:x:/g/personal/{myname}/EaAiX8yXQZ9Anl5ok20zITIB8SfiWvMC...

 

Capture Invalid credentials issues importing data in PowerApps PowerQuery.PNG

8 REPLIES 8
summitb
Level: Powered On

Re: Load data from external data sources using Power Query - constant credentials failures...

You have to set-up on-premise gateway 
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/gateway-reference

But if its a 70mb file may be there might be an issue even if it runs.

lewishm
Level: Powered On

Re: Load data from external data sources using Power Query - constant credentials failures...

Hi @VFXPro1 - it definitely won't work using 'Anonymous' so you need to use account credentials for the account where the file is stored in OneDrive for Business.

 

The file path could also be an issue.  In my experience, it needs to end in the file extension type e.g. ".csv"

 

Give those a try and let us know how you get on.

Yahya
Level 10

Re: Load data from external data sources using Power Query - constant credentials failures...

From the filepath, delete the question mark and all chars at the end of the url, and i guess you need to use corporate account.

 

 

VFXPro1
Level 8

Re: Load data from external data sources using Power Query - constant credentials failures...

Thanks @lewishm

Perhaps, this might sound stupid, but how do I get a path that ends with ".csv" when the link provided by OneDrive for Business is just an ID??

 

Also, since the file resides in O365, do I still need a data gateway (I didn't think so).

lewishm
Level: Powered On

Re: Load data from external data sources using Power Query - constant credentials failures...

@VFXPro1 - you need to get the file URL by selecting the file and clicking the "Copy link" button.  This should give you something like this:

https://<companyname>-my.sharepoint.com/:t:/r/personal/<useraccount>/Documents/Attachments/test.txt?csf=1&e=4eMMLy

 

And you will need to delete anything after the file extension, so for the above URL remove "?csf=1&e=4eMMLy" that appears after ".txt."

 

And you're right, if your OneDrive for Business is in O365 cloud-hosted no gateway will be required.

VFXPro1
Level 8

Re: Load data from external data sources using Power Query - constant credentials failures...

Great tip @lewishm, the copy link does provide the name of the file.  Smiley Happy

Sadly, I'm still getting an error... See the screenshot.    The CSV file is 70 MB and has a little more than 1M row, I'll try with a smaller file just to test my theory.  I wish the error message would provide more value.

 

Capture Error.PNG

 

 

PowerApps Staff CWesener
PowerApps Staff

Re: Load data from external data sources using Power Query - constant credentials failures...

 

@VFXPro1 - can you try opening the CSV file in Excel Desktop on your PC, and get the link using the method described in this article : https://docs.microsoft.com/en-us/power-bi/desktop-use-onedrive-business-links and let me know if that works.

Thanks,
Clay.
VFXPro1
Level 8

Re: Load data from external data sources using Power Query - constant credentials failures...

 Thanks for your assistance.  Sadly, if the CSV file is larger than 1M rows, then it won't open in Excel, thus the suggestion won't work as described.

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Users Online
Currently online: 384 members 4,628 guests
Please welcome our newest community members: