Results 1 to 10 of 10

Thread: Combine Files With Monkey Tools' Smart Folder

  1. #1
    Seeker ParserMonster's Avatar
    Join Date
    Mar 2020
    Location
    Virginia
    Posts
    5
    Articles
    0
    Excel Version
    Office 365 PRO

    Combine Files With Monkey Tools' Smart Folder



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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:
    Code:
    // 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
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	PQ Combine Files with Smart Folder.png 
Views:	6 
Size:	82.2 KB 
ID:	10345  

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,448
    Articles
    44
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,448
    Articles
    44
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    Seeker ParserMonster's Avatar
    Join Date
    Mar 2020
    Location
    Virginia
    Posts
    5
    Articles
    0
    Excel Version
    Office 365 PRO
    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

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,448
    Articles
    44
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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!)
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Seeker ParserMonster's Avatar
    Join Date
    Mar 2020
    Location
    Virginia
    Posts
    5
    Articles
    0
    Excel Version
    Office 365 PRO
    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?

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

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,448
    Articles
    44
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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!
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    Seeker ParserMonster's Avatar
    Join Date
    Mar 2020
    Location
    Virginia
    Posts
    5
    Articles
    0
    Excel Version
    Office 365 PRO
    Hi Ken,

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

    Cheers,

    Mitch
    Attached Files Attached Files

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,448
    Articles
    44
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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!
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  10. #10
    Seeker ParserMonster's Avatar
    Join Date
    Mar 2020
    Location
    Virginia
    Posts
    5
    Articles
    0
    Excel Version
    Office 365 PRO
    Hi Ken,

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

    Cheers,

    Mitch

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •