Power Query - Data from many folders with Single Query

Рет қаралды 12,680

Жыл бұрын

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
learn.microsoft.com/en-us/pow...
My Linked in Profile
/ james-botes-basensei

Пікірлер: 24

@katewickersham4928 Жыл бұрын

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

@alexrosen8762 Жыл бұрын

Fantastic tutorial. Very insightful and useful 🙏

@Imopanemo 5 ай бұрын

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.

@user-gh4zy8gu6q 11 ай бұрын

Amazing. Really helpful!

@thinmya8972 Жыл бұрын

very cool trick

@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 4 ай бұрын

@@basensei8699 True!!

@kebincui Жыл бұрын

Cool 👍

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

@pascaljoly5752 5 ай бұрын

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 4 ай бұрын

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!!

@claudio1028 11 ай бұрын

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?

@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 7 ай бұрын

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

@dragisadjedovic6505 2 ай бұрын

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 2 ай бұрын

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 5 ай бұрын

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 5 ай бұрын

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!