Power Query - Data from many folders with Single Query

Powerquery allows you to pull files from folders. But you'll run into some limitations using the out of box functionality in terms of performance and the number of queries. In this video i show how to pull data from multiple folders in a single query using custom functions.
#powerquery #m #mquery #excel #data #dataanalytics

Links
Excel.Workbook Function

My Linked in Profile

[katewickersham]

This was incredibly helpful. Thank you so much for posting!

[Imopanemo]

This was very helpful - I've been looking for something that will a) use folders names in data b) use sheet names in data c) use file names as data d)to be able to define which exactly folders are to be used. Because I have different types of data, I will use 3-4 of the above, but data will have much better structure.

[alexrosen]

Fantastic tutorial. Very insightful and useful 🙏

[bushehrnoshishi]

This was so much work for a function that Power Query performs on its own. Just Get Files from the folder one hierarchy up and you can do it in what 3-4 steps rather than 9!

[apiphabibjet]

The power query tricks are very cool, at least I do the address of the folders to work with vba, I subscribed to your channel, it's very good, the possibilities that power query gives us are almost unlimited to deal with this type of problem and save us many hours of work.

[pascaljoly]

good tutorial. i knew how to combine multiple files with multiple sheets from from the same folder, not from multiple folders. not sure i'll have a need for it at this stage but i kindda want to just so i can practice :) thanks a lot

[LeithSymes]

Hi James, Is there a way to do this in a folder that contains excel, pdf, word docs etc that we obviously don't want PQ to look at?

[claudio]

Hi! Thanks very much! Is it possible for a folder with csv files?

[aparnac]

Hi, I tried this solution with locally stored files. It works well. However, I tried using SharePoint link, it is giving me an error. Could you please help me use this solution with SharePoint folders? Thanks

[rogervandecraen]

very interesting and well clear explained. what if each of the files in the subfolder is pasword protected does this still works? If not is there a workarround

[pascaljoly]

I have noticed that i can add folders and i can then refresh the query but if i delete one, the query doesn't work. it gives me the following error message: "We cannot convert the value null to Type text". is it normal? i'd have thought the query shoudl update automatically based on the folders we have.

[aqgh]

This is very useful. So hard even a caveman can do it.

[vishalgaikwad]

@Ba sensei can we do it without combining them?

[dragisadjedovic]

Hi,

Great video. Great tutorial.

However, i am generating dataset from multiple folders (on File Server) with this method and it works fine in PBI desktop. However, when i upload it to PBI Report Server, and perform scheduled refresh (or just refresh it ad hoc) it gives me message "This report contains dynamic datasources, which cannot be refreshed in Power BI Report Server."
Can you maybe help with this. Any kind of suggestion will do.
Thanks