I should not be surprised by now, but once more by jaw dropped while watching one of your tutorial. You got me again, Mike. This is wonderful and fantastically laid out step by step. Clear and simple. Thank you Sir. 😀

That's a great tip Mike! Thank you for a wonderful tutorial.

You always have an uncanny way of posting videos that solves a problem I'm currently having. Great video!

That's great tip to users, as always great video, clear, simple and useful.

Thanks Mike! Just tested it with several exports from the system and it works perfect! Already part of the company report. :) Really-really useful, thank you!

Can't thank you enough for this magic trick and the so many tricks I have learnt from you!!

Topics: 1. (00:23) Introduction 2. (02:36) Excel Power Query Example of Parameter for Data Source / Folder Path 3. (8;48) Power BI Example of Parameter for Data Source / Folder Path 4. (10:23) Summary

Another great video Mike... really appreciate the way you present it. I Love Excel :) Thanks Mike

Thanks Mike - Awesome! Have a quiet week so planning to use to work through a few of your videos.

Hi Mike, we use the parameter to enable users to use the file on their own Box Sync folder. In the Excel sheet we have this formula: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)&"Data\" which enables the parameter to know the path to the file with the different usernames in the path.

Excellent, Mike! I need to use these folder path parameters in files that I've shared in Onedrive, like a stock portfolio. In the Excel file, the user simple picks which computer and a table containing the various paths is used as the parameter and the query works seamlessly.

A few remarks here: - first off: really great video! (as always) - next: the sheer efficiency in your queries is ‘ridiculous’: the whole parameterized import of files in a folder in just one step?... Awesome!! (And: yes, Referencing queries is part of that super-efficiency, too! You don’t want to repeat the steps you’ve done in another query already.) - finally: the parameter trick in PBI-D is also available in Excel-PQ (but you may have to activate the use of parameters in forms in the options). It’s super fine to be in this class!

you are always wonderful without fail.

Thanks Mike.. great tip for dynamic folder paths. Will have to study this further. Thumbs up!

I don't know how you do it but I love your videos Mike. They are precise, beautifully explained and very useful. Just one word: "EXCELlent" :P

Excel-lent Mike! Thanks for the continued great videos!

The most useful and clear tip!

What a great video that I really was waiting for thank u so much Mr. Mike you are the best

Thank you so much for your amazing videos!

Great video Mike, thank you!

Thanks Mike, another great video.

Great! You helped me a lot. Thank you very much, and congratulations for the excellent video lesson.

Thanks a lot it was exactly what I was looking for!

Great Video as always Mike, thank you so much for

This made my work easier! Thanks for this!

Just what I neeeed!!! Thanks from Russian Power BI fan)

Very cool tip! Thank you Mike!

Awesome as always! Thanks for dynamic fun :)

Great stuff Mike, thanks.

Amazing trick. thanks for sharing folder location fun..

Awesome video, thank you!

Awesome mike, thanks a lot.

Thanks for a great manual, but I have an error on final combining files (double down errows in Content). It searches the Sample File for a table, which holds the path of the source folder. Do not know why and how to go throuh it. Thank you for any help on it.

There is an issue attempting this with xls files. (csv and txt files were no problem) I replied to a previous comment but pasting it hear for more visibility. My only workaround was to do the following (until a more efficient solution is presented): 1) Perform a "Combine and Transform" using the folder location of the xls files 2) Change the Source to "= Folder.Files()" -- Note: replace with the name of the query that includes your folder location. 3) Go to the "Transform from..." folder created in step 1 (usually includes Sample File, Sample File Parameter, Transform Sample, and Transform Function). 4) For each query, go to the Advance Editor and copy the contents and paste all lines of code in MSWord. 5) Manually create each query in it's own "Transform from..." folder 6) Go back to the original query (in step 1) 7) Adjust the Applied Steps (Invoke Custom Function, Removed Other Columns, Expanded Table Column) by replacing the old Transform File query references with the new ones created in step 5. Hope this helps! Please reply if a more efficient way to do this is available.

What a relief. Thanks once again

It is very useful. Thanks you very much.

Thanks for your great work and tricks. This was so useful for me.

Very informative!

Excellent one... thanks for sharing

Amazing......simply amazing!!!!

Hi Mike, thanks a lot for the video. How should I proceed if I have already completed transformations and would like to use this technique later. Speaks I already have a query and the corresponding sample queries (first file) in Excel

AMAZING TIP. THANKS SIR

Thanks for the video!

Great video as always, any way to have a folder located in sharepoint ? Can't find a way to get a specific sharepoint folderwith power query , microsoft lock the thing and we need to filter all the folders from root wich is long and painfull

Thanks for the great video!

Great Trick and very useful.

very important for automat process ,thanks

Whenever I dropped another file into my folder, and I hit refresh... it's only grabbing 1 of the reports instead of both. In fact, it grabbed the most recently added file. I want it to grab everything from the folder. Also, if I tried hitting the 2 arrows down it says we couldn't find the table named table2.

A good video. Thank you.

very helpful thank you

This is very helpful, can we do this but with more than one file path on the table?

Well done!

Thank you Mike, this is very helpful. If I have another row in that folder path table as "SalesPerson" (for example, I have different data import calculations for different sales person), then bring in and use this "SalesPerson" in the PowerQuery, is there a way to avoid that data firewall error? If I have a line in the first query like SwitchA = "PersonA", is there a way to pass this SwitchA (and its value) to other queries in the same file? Many thanks...

This is great. Any tips on what to do if your data is in a table in Excel files? When I attempt to expand the "Binary" column I get "We couldn't find an Excel table named 'FolderPath'."

Kudos!!

very cool technique!

It was awesome video..

Thanks Mike 👍👍

❤️great trick

Thanks Mike for the video. I like the one line of code for dynamic path. I have tried uploading multiple text files from a folder but PQ doesn’t consolidate all files although it recognizes all of them in first window when file types are returned / detected.

Thank you..Awesome!!!

Thanks Mike. I use this way to accumulate files but to be easier i add a formula to path folder table (cell("info") and remove its file name with replace formula) in this way user dont need to put path folder just summary file should be in same folder of other files.

Thanks Mike :-)

Hi This is great! I followed step by step yet when I changed the folder path, refreshed, and got error- "The following data range failed to refresh: ExternalData_1 Continue to refresh all?" then "[Expression.Error]: The key didn't match any rows in the table", I wonder if it is because my original data files are .xlsx not csv. or text.

Nice. Thanks

Works until I get the files in power query (after Folder.Files step). But the moment I expand the files, it says we couldn't find an excel table named 'table1' . Tried everything but it did not work. Is this a bug?

Gr8 , you r master

Thanks you so Much

Hi, I think they have updated PowerQuery for Excel so that this trick or work around doesn't work any more. I still get the Formula.Firewall error following your steps exactly. Have you checked it out if this still works after the latest Power Query Excel update. Anyother way I can still do it ?

Great explanation for both platforms. It can be also useful for users who share the same Excel file on a shared folder, but the user name in path is different.

Hi Mike, I following the same step for Dynamic excel folder path as shown in video. In your case, I can see power query automatically generating sample file, functions once you click on the double expand button. But In my case , once I create refernece query from the folderParameter query > right click on content column > Remove Other column > Click on Double Expand Button , it give me error. It say "we couldn't find the excel table name "path". And Also I don't see all those auto generated steps.Any idea how to fix this issue.

Tried this for excel files(existing query), it says "We cannot convert a value of type record to type text". Please advice for the same! Thanks!

Is it possible if the Folder Path is Blank, skip the query update?

Very nice

Hi. Does this process work with one drive. Not had this issue yet, has my reports are read only, but need to cover off if issue is raised. Thanks Martin

👌👌

I need help, all excel formulae use (;) instead of (,) as separator. How can i Reset it to work normally? i am using office 365

I try to follow this video for combine multiple excel files but not working, please help.

I am still getting the Formula.Firewall error. I added the Folder.Files(MyPath) but the error is still there.... read a few articles on the error and not sure why I am still getting the error. i saw Syed Aqueel was also getting the error. Any thoughts?

Double expand doesn’t work for me (I have excel files in the folder). It says we cannot find the table name table2. Anyone know how to solve this?

top

I cannot get this to work for data from a sharepoint folder. Because you enter the URL then navigate to the folder. Is there a solution?

How can you apply this trick to xlsx files! I tried this but it is not working. Thanks

Can you pls explain this method if we use one drive for example, because it gives links to the files , not path 🙏

Hi Mike, I Use This Formula To Find Dynamic Folder Path(LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)&"Data\") in Excel Power Query.Do you Know Any Same Solution Or Formula For Power BI? Thanks Alot For Everything.

Hi Mike can you please do a video on creating and income and expenditure statement in power bi. Thanks

Did I miss the step where the file path was first created?

Thanks, I have a question - I have to create multiple reference queries (source file contains multiple tables that should be processed through power queries). Every time I create a new query from the referenced source it automatically creates the Transform File from Query Folder, Is that the case? thanks

did microsoft just patch this trick? seems like it just stopped working

is there a simpler way in power Bi :((