Case Scenario: You have a file on a Dropbox folder that contains Power Query connections, and you need those connections to work regardless of the computer from which you open that file.
Solution: Replace the path string in those queries by another string produced by another query that reads the location of the file when you open it.
This video explains how you can:
– Use the CELL formula to indicate file and folder paths
– Create a query in Power Query that produces a text string
– Replace the path string in the M code of a query
– Change the privacy settings in Power Query to allow file and folder paths defined by queries

The example on this video uses the scenario built on previous posts:

Selecting all Columns of a spreadsheet with Power Query does not get dynamic

Get the data from the most recent file in a folder with Power Query

Other techniques:
By Ken Puls -Power Query Errors: Please Rebuild This Data Combination 
By ExcelTurbo – Como extrair arquivos de pasta dinâmica no Power Query com linguagem M

Caution when disabling Query Data Privacy checks, especially if you are using Power Query in Power BI – read the blog post “Data Privacy Settings And Data Refresh Performance In Excel And Power BI” by Chris Web, especially the last two paragraphs and the other post he mentions there.

Check the discussion on Microsoft’s UserVoice platform about the firewall issue, and vote to get the Excel and Power Query team fixing this issue.

Learn Excel and connect with a community of Excel professionals and enthusiasts: