How to use Power Query to connect to a file on OneDrive or SharePoint
Wyn Hopkins
Leading a team that solves data challenges for companies and people Power BI | Excel | Fabric | Solver Budgeting & Consolidation. Control costs, reduce risk and assist growth Microsoft MVP ??5 Million+ YouTube views
A growing number of organisations are starting to store their files on OneDrive for Business and SharePoint. Users will begin to discover that connecting to these files with Power Query is not as straight forward as clicking the button for Get Data from Excel Workbook, or Text / CSV.
There are a couple of options which I'll demonstrate in the video:
- From Web
- From SharePoint Folder
I'm open to suggestions on improvements. In fact I include an improvement on option 2 that I picked up from Miguel Escobar.
Neither option 1 or 2 is great. It's fraught with traps and as you'll see I mess up and have to restart... and I'd classify myself as an "experienced user" !
Please vote for a better experience on either / both of the Excel UserVoice site and the Power BI Ideas page.
Plus it's about time there was a single Power Query User Voice location.
I hope you find it useful.
The previous video I did on this topic is by far the most popular on our YouTube channel, and I felt it needed expanding and more explanation to include the SharePoint Folder approach.
Remember to vote!
Thanks for watching
Stay safe
https://accessanalytic.com.au/blog/
Wyn
Manager, NPAS at Immigration, Refugees and Citizenship Canada
4 年Thanks for the video. Definitely not user friendly! Until Power Query (PQ) team comes up with a solution, this might help end users if the document is updated on a recurring basis such as a bank statement: 1) PQ developer sets up VBA to let user browse through file path and get the full path of the file as if they are browsing through network drive. Than VBA code changes the active cell value to that path. The active cell needs to be a named range or part of the table. 2) PQ dev needs to break down file path into several cells with name ranges 2) A PQ function required to import these named range in. 3) These named ranges are embedded in the Advanced Query editor... This might make end users life easier but still this requires login and a lot if settings and adjustments... Hopefully, we will have better UI and experience with connecting to files in Sharepoint soon.
Digital Architect, Microsoft platform & Business applications (Dynamics 365, Power Platform, Azure)
4 年Is there a way to save the query as some settings file/ xml ? I came across few blog articles but nothing clean!!