Combine Files With Monkey Tools' Smart Folder

[ParserMonster]

Hi All!

I am trying to create a general-purpose Power Query that retrieves all .XLSX files from a folder.
I Injected the Smart Folder Query, which puts the necessary ingredients in place.
When I try to combine files, I get this error:

We couldn't find an Excel table named 'XLGQuery_Parameters'.

Here is the query so far:

// Query written by Ken Puls, FCPA, FCMA, MS MVP (Excel) (www.excelguru.ca)// Injected into this workbook via Monkey Tools at the model designer's request
// For more information, Or to purchase a copy of this add-in, visit http://www.excelguru.ca
//
let
    Folder = fnGetParameter("File Path"),
    Source = fnSmartFolder(Folder),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "Source\")),
    #"Lowercased Text" = Table.TransformColumns(#"Filtered Rows",{{"Extension", Text.Lower, type text}}),
    #"Filtered Rows1" = Table.SelectRows(#"Lowercased Text", each [Extension] = ".xlsx")
in
    #"Filtered Rows1"

If I just use a hard-coded folder location without the smart functions, the combine files functions properly. I found a really confusing suggestion here, but I want to know why the smart folder is failing.

Cheers,

Mitch

 

[Ken Puls]

Hmm... that's strange... if you didn't have that table, it should have created it for you automatically when injecting the SmartFolder setup...

Did you get a new worksheet called "Query Options" injected into the workbook as well? That worksheet holds the required table...

 

[Ken Puls]

Ugh, I believe I've found the issue. I think something may have changed here, but it's the combiner that is complaining due to the structure, and it appears to be specific to combining Excel files.

So here's the thing... you can work past this. When you get to that screen, right click the "Parameters" folder and choose "Transform Data". That will let it complete, although it will attempt to combine all worksheets in all files by default. You can still modify the Transform Sample to fix that though.

Hopefully that helps.

 

[ParserMonster]

Hi Ken,

That certainly is interesting! Right-clicking gets me to the dreaded Formula.Firewall. I remember reading about that so I know there's a fix, if you could point me to the chapter in the book?
Also, I learned about modifying the Transform sample but that is tricky to follow the steps. I don't want to do procedures that may have become outdated due to new releases of Power Query.
So, if you have a recent link for Transforming Sample, I would really appreciate it.
Are these things taught in the advanced course? I'm still doing intermediate.

Cheers,

Mitch

 

[Ken Puls]

Right-clicking gets me to the dreaded Formula.Firewall

Wait, what? The whole point of the SmartFolder query is specifically to avoid this issue, and be able to smart switch between a local folder and the SharePoint equivalent. You should be prompted when you first set it up to declare the privacy settings (organizational, etc..), but once done that should be it.

Would you be able to share the query structure you have with me? Go to Monkey Tools -> Import/Export and choose to Export Queries. Upload that here and I can take a look at what you have happening.

With regards to the Transform Sample, the best and most complete material we have is in the Essentials course of the Academy. I'm actually working on updated chapters for Master Your Data, but they are still not ready for sharing at this point. In truth, if we can get you past the formula firewall and into the Transform Sample, you should be able to drill in to the Excel table you need and transform it. Return to the master query, delete the Changed Type step, and everything should just work. (I say should, because this is real life - of course!)

 

[ParserMonster]

Hi Ken,
I've attached the file and pasted the text here.
If you needed the worksheet, I can redo the export.
Thanks for helping me. By the way, could OneDrive or my version of Excel be the culprit?

C:\Users\mitch\Downloads\Data Dedupe 2021 02 18b-Queries.txt

 

[Ken Puls]

Hey Mitch,

Can you upload that text file? (Click the Go Advanced button to reply, then Manage Attachments to add the full text file.)

Thanks!

 

[ParserMonster]

Hi Ken,

I tried that yesterday. Technology hates me. I think I managed to attach it this tim.

Cheers,

Mitch

 

[Ken Puls]

Hi Mitch,

Okay, I think I've worked it out. The formula firewall was tripped by missing one step in the way I'd intended the SmartFolder setup to work.

If you want to re-create from scratch, here is the intended set up steps:

• Save the workbook somewhere before you trigger this function
• Go Query Monkey -> New SmartFolder query

That should create all the required components for you, including the Parameter table. The secret is having the workbook saved first, as the file path doesn't get generated in the Parameters table until the workbook is saved.

Now, once you've injected the SmartFolder setup, edit the FilesList query, and set your security as needed if prompted. You can do any extra filtering in that query, but don't combine files directly in the FilesList query. That will trigger a formula firewall rule at the end, which we don't want. Instead:

• Expand the Queries pane on the left
• Right click the FilesList query --> Reference
• Rename the new query to be something logical
• Then combine the files from there

You'll still need to right click the folder in the sample window, but that should create things properly for you.

I've attached a sample for you, where I called the final table "Transactions" (just like I do in the Academy stuff). If you go with this workbook, you can filter the Fileslist Query into a specific subfolder, then go to the Transform Sample File, drill in to the worksheet/table you need and you should be good to go.

Hope it helps!

 

[ParserMonster]

Hi Ken,

I appreciate your help with this! Your solution worked perfectly!

Cheers,

Mitch