Power Query - Data from many folders with Single Query

[@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.

[@katewickersham4928]

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

[@alexrosen8762]

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!

[@basensei8699]

fair enough ! those default queries using the ui are often much slower especially on massive datasets. I've had some clients with very large datasets pulled in via the default UI method sit with massive performance issues.

[@bushehrnoshishi]

@@basensei8699 As I aren't allowed to use DAX Studio at work to test the performance of my code vs PQ UI I will always opt for what feels faster - either coding or UI. But sure in case of performance issues your code will be of great help :)

[@apamwamba]

@@basensei8699 True!!

[@thinmya8972]

very cool trick

[@apiphabibjet2863]

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.

[@basensei8699]

excellent thanks Apip ! i tell you Power query is just to amazing! the things you can do with it it almost endless.

[@pavel_stafeev]

Amazing. Really helpful!

[@pascaljoly5752]

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

[@apamwamba]

You can easily do it from many folders i.e subfolders but BA Sensei HAS DONE IT WITH ONE QUERY and at the same taching me how to use custom functions. BA SENSEI is COOL!! Really need to learn custom functions and parameters...WOW!!

[@kebincui]

Cool 👍

[@rogervandecraen9797]

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

[@basensei8699]

passwords have a workaround. but i'll put it in another video at some stage.

[@rogervandecraen9797]

@@basensei8699 thanks for working on a video on that problem, can 't wait to see it and solve my problem.

[@rogervandecraen9797]

@@basensei8699 thanks for initiative, can't wait to see it. hopefully it will solve my problem.

[@claudio1028]

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

[@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?

[@vishalgaikwad6333]

@Ba sensei can we do it without combining them?

[@basensei8699]

yes indeed!

[@aqgh1]

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

[@basensei8699]

glorious!

[@aparnac48]

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

[@basensei8699]

ill see if i can make a video on sharepoint next

[@karthikvu452]

Hi, did u get it how to do it using sharepoint?

[@dragisadjedovic6505]

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

[@basensei8699]

ooo thats maybe a good one for me to do in a PowerBI Service. I've had some of those issues also recently!

[@dragisadjedovic6505]

@@basensei8699 thanks in advance. I'm looking forward to that video, because this is a classic example from everyday practice

[@pascaljoly5752]

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.

[@pascaljoly5752]

it's ok, i found a fix. i reloaded the table that includes the folder names and paths and as i had deleted manually one of the folders, it showed a row with null values in power query which i filtered out. now, it still works if i add a folder but if i delete one, it works too!