cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Kudo Collector
Kudo Collector

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
Highlighted
Advocate II
Advocate II

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.

Highlighted
Helper I
Helper I

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.

Highlighted
Kudo Kingpin
Kudo Kingpin

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.

 

 

Highlighted
Kudo Collector
Kudo Collector

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).

Highlighted
Helper I
Helper I

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.

Highlighted
Kudo Collector
Kudo Collector

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

 

 

Highlighted
Power Apps
Power Apps

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.
Highlighted
Kudo Collector
Kudo Collector

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
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Users online (9,257)