Import SharePoint File into Power Query

Importing a Excel file that is hosted on SharePoint or OneDrive should be as easy as copying the URL from the browser or when you use the share feature in SharePoint / OneDrive. Unfortunately, it's not so straightforward. First, the URL link from the share feature isn't event the right one to use and then you have to be on the online version and request to open it with the desktop version to get the correct URL. After that it's just a copy and paste of an edited version of the URL to put into the Power Query field. It's sounds like a lot but once you see how it's done, it's not so bad. Hopefully Microsoft will make it easier in future Power Query updates. But in the meantime, check out the video to learn how to import a file hosted on SharePoint into Power Query.

Solve the Formula.Firewall Error in Power Query

The Formula Firewall error is a data privacy error you might get when sharing Power Query Excel files and it's basically Excel's way of saying you have an external data source when it seems like you don't. One way to mitigate this is to change the privacy setting to ignore this message, but when you're collaborating with other, they might not feel comfortable doing this. The other option is to set up a bridge or staging querying. This involves getting into the M code, but it's not so bad. This video talks about the table that takes in user input on where the folder path is for a file but those detail instructions are in another video at https://youtu.be/ignTb_UozTw. This one will cover mostly on how to solve the Formula Firewall issue.

Share Power Query File - Formula.Firewall Error Workaround

When you're sharing files that use Power Query and it sources a data file from a separate workbook, you might come across multiple issues like the DataSource.Error or Formula.Firewall error message. A way to get around these issues is to ask the user you're working with to change or update a few settings. It's not too complicated so check out the video to learn how.