cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
VFXPro1
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
summitb
Power Participant
Power Participant

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
Helper I
Helper I

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
Kudo Kingpin
Kudo Kingpin

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.

 

 

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

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

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

 

 

 

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

 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
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,276)