The Filename() portion is calling a function to return the desired filename from a workbook table, (based somewhat on this approach.) We already know that works, but we also know that this is definitely pulling data from an external workbook (even it the file path is for this current workbook!) And to be fair, this would be the same if we were pulling from a web page, database or whatever. Source=Excel.Workbook(File.Contents(Filename())), Looking at the first line, it is made up as follows: But it tries to merge that to an external data source which is called in the first line. Notice the issue here? The Merge step near the end references a query called DimShipper. Let’s have a quick look at the code that my friend sent: The answer is that you cannot combine an external data source with another query. So why when you do some merges does Power Query throw this error? For anyone who has worked with Power Query for a bit, you know that it’s perfectly acceptable to merge two queries together. This message is a bit confusing at first. Please rebuild this data combination.” What Does This Mean? He was a bit frustrated, as he got a message from Power Query that read “Formula.Firewall: Query 'QueryName' (step 'StepName') references other queries or steps and so may not directly access a data source.

I can now ask to put the merged table (1 table) into PowerQ editor.I got sent this today from a friend.

In this window, I can decide how to combine them, and once I am done with that. Now I open that area (buffer/Connection) by going to Data->Get Data->Combine Queries. Now both Queries/Tables in the buffer area (not PwerQ editor nor Excel Sheet). Now I can go and 'Get' the second table and do exactly the same, load it into the Buffer area (Connection area). So I 'Get' the first table, but I need the second one? Before 'Get' the second one, I move the first one in an area/buffer, lets called it 'Connection.' Now my first Query in the area called Connection (not in Power Q editor Nor in Excel) Since I need to work on 2 tables to merge them, I can not put both of them in the PwerQ editor at same time, nor I want to 'Load To' both of them to Excel since I need to work on/transform them first. Today I was thinking about it and came with this conclusion, not sure if I understand it correctly or not.

Power query excel 2021