63 - Fixing the Formula.Firewall-error (Power Query)
You might want your user to be able to input a filepath in a cell, that's used by Power Query to find the relevant folder or file.
And you might want to store this extraction in a separate "find the filepath"-query. I do. However!
Introducing the Formula.Firewall-error devil!
The devil doesn't want you to use the result of one query, as a source in another. You can't have Query #1 give you a filepath, that you load in Query #2 in your Excel.Workbook() step.
I show 2 ways to vanquish the vile devil.
Solution 1: Don't actually use the source query directly.
Use the source query to show what steps you're using. This is relevant if you need to eg. clean up the filepath (maybe you have to add a "\" at the end).
But you instead have a step where you load the cell again, or directly load the cell inside your Excel.Workbook() step.
Solution 2: Instead of loading the filepath as text, actually fully load the folder/file.
If your filepath is to a folder, use the Folder.Files() function to actually load the folder, inside your source query.
Because the output of Query #1 is now a table and not text, you're allowed to reference Query #1 like normal.
00:00 Intro
00:14 Name your range!
00:49 Turn a named range into a text-value
01:10 Encountering the Formula.Firewall-devil!
02:22 Solution 1 - Don't actually use a source-query
02:43 Method 1: Write the code directly in your source step
03:27 Method 2: Add a step before your source step
04:35 Power Query understands 'variable scope'
05:08 Method 3: Inner Query
06:23 Solution 2 - Load the folder found from the named range
07:39 Method 1: The wrong/annoying way
08:22 Method 2: The right way
09:42 Which solutions I prefer