Connectivity: connecting to Excel file: Sharepoint folder vs Web

Do you expect to load only a single Excel file from Share Point?

If not, I would start with the Share Point Folder connector, apply a filter and load the specific files.

If you have only one file and want a quick solution, keep using the Share Point Folder connector, and filter the folder to get the specific file, then apply drill down to import the Binary of the Excel file. Power Query will automatically identify that this is an Excel and will enable you to do the transformations on the Excel if needed.

If you have some time and would like to do it more efficiently, I would use the Web Connector and get the file URL following these steps (I published it in detail in Chapter 8 of my book, Collect, Combine and Transform Data Using Power Query in Excel and Power BI).

Step 1: Go to the Share Point folder and open the Excel file in your browser (Excel Online):

Step 2: In Excel desktop, go to File tab and click on the file path. Then select Copy path to clipboard.

Step 3: Now, in Power BI Desktop, select Get Data --> From Web and paste the path. Next, delete the suffix "?web=1"

Hope it helps,

Gil