There is a couple of ways how to connect to the SharePoint file with Power Query, but first of all, I recommend the connection through SharePoint Folder. It is more versatile and can be used successfully with different file formats – CSV, TXT, Excel, or JSON.
How to connect o SharePoint with Power Query (or similarly with Power BI)
1. Copy your SharePoint site address. It looks similar to this https://mycompany.sharepoint.com/sites/My-reports
2. Go to the Data tab, choose From File and From SharePoint Folder.
3. Paste previously copied URL.
4. There are multiple authorization options, but the Microsoft account worked for me. Because I had logged in before in other services, I clicked on Sign in, choose my account, and connect.
5. After that, you will see all available files on your site and use the Transform button to unleash Power Query and choose the file that you want to use.
6. Click on the Binary of the file that you are interested in.
How to fix incorrect Power Query connection
In a rush, there might be mistakes in the connection process to the data source. The problem is that Power Query or Power BI remembers it even it was incorrect.
Go to the Data tab, click on Get Data, and then Data Source Settings. Find that connection, clear or edit.
How to fix incorrect Power BI connection
In Power BI it is a little bit different as it Excel Power Query.
An alternative way to connect to SharePoint file with Power Query (or similarly with Power BI)
Another option is to connect to a specific file type with Power Query. First of all, you should obtain the correct path to the file (without extra symbols that might make problems for other users). When you are in a specific SharePoint directory, do the following steps.
SharePoint file path without extra symbols
1. Select the file in SharePoint.
2. On the right side, look for the information icon (Open the details pane) and click it.
3. Go as low as possible in the details pane, locate the Path section, and copy.
When you have a valid path, then connect to your file as you usually do. The only difference is that you will need to authorize.
Leave a Reply