Problem with referencing a List in a PowerQuery

[lynnet]

Hi all,

So I am trying to create a query that will pull content from a folder containing multiple Excel files. I only want the content of tabs within those files that have a name that appears on a master list I've created. Let's say that the source files have a variety of content, but I only want to pull from tabs in any of the files that are called either "DATA" or "IMPORT"... but I need that list to be able to be maintained/dynamic so I can add to it if necessary, e.g. "UPLOAD".

I found this YouTube video which perfectly achieves it:

... and I have been able to replicate it, if I create the master list of tab names 'directly' using "Enter Data" into a query as follows:

= {"DATA", "IMPORT"}

I've called that list "Tabs".

However, if I create a list on the face of an Excel tab in the workbook (for easy maintenance), query that, convert it to a List and call it "Tabs2", and then change the reference for the lookup from "Tab" to "Tabs2", the main query breaks. The list of values that was previously saying "Table" and could be expanded, switches to hard code, the word 'Table' between square brackets, and can no longer be expanded.

Any ideas on why this might be please? Thank you!

 

[JoePublic]

What does your query look like now?

 

[lynnet]

OK, so the issue I have is that I can't expose any of the content here for security reasons. I've just tried to replicate the problem with some dummy data (intending to attach the files here) and - hey presto - unfortunately it worked just fine. So now I'm scratching my head for how to show the problem.

This is the beginning of the M for the main query:



When it runs the 3rd line above, it results in these columns on the right hand end:



When it runs the 4th line, this happens, and the expansion step that follows won't work after that:





It works if the 'Tabs' reference in List.Contains is pointing at a list that's been created using Enter Data, but not if I've picked it up from a worksheet range. However, when I try to replicate the problem with a dummy data set, I can't.

 

[JoePublic]

What is Tabs2? A query? If so, what is its M code?

 

[lynnet]

Yes, Tabs2 is a query on a table in a worksheet in the same file, & the M code is:

let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
Column1 = #"Changed Type"[Column1]
in
Column1

I've just done a test where I pointed the main query to source files saved in a folder on my C: drive instead, and it worked fine - then when I pointed it back to the company network drive, it failed again. What does that mean?!?

 

[lynnet]

Hi Joe - thanks for your engagement with this so far, it's doing my head in ;-) I've created a version that I can share, and attached. If I flip the 4th step back to "Tabs", which is a direct-entered query, it works. If it uses Tabs2, which is consumed from a worksheet table, it doesn't. The "Test file" attachments are the dummy data. If I put the "Test file" attachments on my C: drive, then the query works using either Tabs2 or Tabs.

 

[JoePublic]

Hard for me to test, since, as you mention, it works with the files saved locally. Do you have any privacy popups when looking at the query?

 

[lynnet]

Yes I did - it did ask me to set privacy levels for the network locations I chose, and not knowing the context, I set them as "Private" because that seemed 'safest'/most restrictive, erring on the side of caution.