In the Excel ribbon, click on the File tab, and on the Info screen that appears click on the label under the file name. There’s no visual indicator that this label is a menu button until you hover over it, but when you click on it, a menu appears with a “Copy path to clipboard” option.
Clicking “Copy path” will give you a path in this format:
https://organization.sharepoint.com/path/path/path/NicelyEscapedFileName.xlsx?web=1
With this starting point, all you need to do is remove the ?web=1 query string parameter at the end of the URL, and you have the path to the workbook that Power Query needs.
Choose “Web” as the data source in the Get Data menu, and paste this URL into the Dialog:
Power Query is smart enough to understand that this URL is pointing to an Excel workbook, so from this point on, you’ll get an experience where you can select tables and worksheets, and can start manipulating and analysing your Excel data.
