Power Query – How I Connected to a SharePoint file ******************
- Open a browser ... by default this opens our Agency's website
- Left-Click on the “waffle” icon to the left of the word: SharePoint
- Left-Click on “OneDrive”
- Left-Click on “Shared”
- Find the desired file name and double-click on it … this will open it on a web page
- Click on the “Editing” drop-down arrow and Left-Click on “Open in Desktop App”
- Left-Click on “Open Excel” … this opens the file in Excel … the file is now open in your browser and in Excel
- Close the version in your browser
- In the Excel version, click on a blank cell (but not cell A1) and enter the formula =Cell(“filename”,A1) and hit enter … this returns a full filename
- Copy this formula and Paste it as a Value.
- From the end of this value, remove the worksheet name and the brackets surrounding the file name … what’s remaining is the desired file address
- Copy this remaining value to your clipboard (verify it is copied to your clipboard by pasting it in another blank cell)
- Now delete the data from the two (2) blank cells you used above and close the Excel SharePoint file you opened above
- In the Excel file you want to connect to the above file, click on the ribbon: Data, group: Get and Transform Data, button: Get Data, From Web
- Paste the contents from your clipboard and click OK … if asked, click Organizational account and then “Signin”, click on your account, and then click Connect
…. The rest is just like any other Power Query
