Awesome! Thank you! Found & learned PQ a few years ago when it wasn't popular at all and when PQ was still an add-in to Excel. Gave up after a few months due to lack of learning resources for M language - I needed to make things dynamic since I work with data from 50+ states and there are so many variations. Recently I found some nice use of PQ for some tasks at work - easier to create and maintain compared to writing codes in R or Python. But the need for M language is still valid. Your video is really helpful! Hope to have time to watch more videos of yours. Thanks for sharing your knowledge!
Thank you NguoiBenLe! If you are interested, you can check our new PowerQuery course with lots of advanced M topics covered! exceed.hr/en/courses/powerquery-fundaments/
Amazing. It is that what I'm looking for. Because, usualy I put all the files into one folder. I will training that. You are a Master from PQ. Cheers from Brazil.
Thank you, Sir. What you have demo is very practical in most aspect of my work for data transformation. Best of all, with these methods, I am enable to allocate designated file paths & speedup the runtime in PQ. Great work. Love your teaching.
Thanks for the info Juan. This technique is versatile and can be used in many different scenarios. The borderline idea is to understand that you can nest custom functions!
Excellent, much appreciated! I’ll have to try this, along with your shared blog to solve ‘table redlining’, in time. Some follow-on thoughts: I tend to want to share these solutions across different groups within the business, I wonder if you have any tips for sharing power query solution “templates”. So far I’ve been trying out ‘Export Connection File’ for functions, which seems to let people pick and choose which saved function template they want to use at any given time. It seems a bit more versatile so far vs sharing an Excel Template document with all the functions pre-built in. I think there is some way to enable a “load organizational shared connection files” but, our org doesn’t seem to have that, so I just ask people to load the connection files from a shared folder. Additionally, I think for this solution I would try to ‘hide’ the first function in the second one by using a second Let-In, that way people only have to import 1 shared connection file to get this to work. Also I appreciate in the video how you highlighted "this is the main take-away". To further make it easy to find the key take-away later, please consider adding a "chapter" to the video which highlights this key take-away (this is a youtube video feature). I also wanted to recommend adding 'Recommended' channels to your RU-vid channel. It seems like channels with high views tend to have lots of featured other channels. I wonder if RU-vid's algorithm uses those channel recommendations to figure out which videos to recommend I watch next. Thank you for the tricks :)
Thank you Hendrick! Haven't thought about sharing Excel PQ scripts as a template. My approach would be to create a parameter table in which each department would paste their location/s before refreshing the data. And thank you for your insights on how to improve reach on RU-vid! We are still fresh in this area, so every feedback is much appreciated! Cheers
Teacher, when I expand the content column clicking in the two arrows Down, the Excel always create automatic folders in the "queries" left side with auto transform function. Question: It's possible to expand ".csv" "folder" with append without create queries folders? It's because I think the PQ It gets very dirty. In this vídeo you create a custom column. I that what need to be done? Thanks any way teacher. Cheers from Brazil.
Hi Felipe, yes, you can create a function that accepts the CSV path as input and work from thereon. There is no need to use a built-in combine function with pressing double down-pointing arrows. The function in Power Query used for that is Csv.Document().
This is amazing! I tried it today and works! Wow! is there a way to ignore a folder that has no data? I have 10 clients (with different folder location) that send selling Infos monthly, but it might be that from time to time there are no sales for a particular month. In those cases I receive an error message that sais no data found on folder xyz. Is there a way to avoid this error and load the remaining 9 clients? Thank you!
I'm trying this with CSVs and it doesn't seem to work. I get a 'field ... of the record cannot be found' when I try to invoke the FXClean function @6:55
Quite amazing solution for files and folders on a “C Drive”. And very clearly explained. But, how to apply the same or a similar solution when data are on SharePoint (Teams)?
Hi, great tutorial. However, I would like to load the data into an existing sheet, but the option is greyed out. What could it be? The source is a Folder.
Hi, First of all thanks for sharing the knowledge. Have question. I work with large data sets conected by power query. And problem is that, whem I make some modifications in power query steps query is refreshing each time what cost a lot of time with large data sets. Is it possible to disable query reffreshing?
Hi Aleksandrs, you can try disabling the background refresh option in PowerQuery, that way you can force to refresh only the selected query, not all the previews.
This is great, and a good intro for me for functions. However, when I do the manual approach (what I was doing before functions), I get the filepath as a column which is very good for doing certain joins (i can use the filepath for dates folders were created, and then use a master date table for current week, or quarters, etc). Is there any way to get the filepath to be included with the approach in the video as well?
Hi, you can get the folder path column by including it in Removed Other Columns step of the second function (the second step of CleanAllRegions function): #"Removed Other Columns" = Table.SelectColumns(Source, {"Content", "Name", "Folder Path"} )
Hi, I tried this power query script, it works as shown with local data. But when I tried using SharePoint and OneDrive link as a path it's not working. It's giving me the following error- [DataFormat.Error] The supplied folder path must be a valid absolute path. Could you please help me how to connect this query to SharePoint folder?
Excel.Objects should return all the objects available in an excel file, including tables, sheets, defined names, and print areas. I suppose your error happens due to the custom function you invoke, so you should check it out
@@ExceedLearning correct I get the error when I invoke the custom function. The only thing that I didnt do like you for the custom function is to unpivot columns. Will try again.
Love the approach, but when I tried it with multiple files from different SharePoint (Web) URLs, I got a Formula.Firewall error. Is there any adjustment I could make to your approach so that I can process a list of SharePoint file URLs without bumping into Formula.Firewall?
So, after experimenting, it seems that to "avoid the formula.firewall", the functions should be embedded one inside the other, so that the query accesses the files to be processed, then processes each workbook using an in-line function, which in turn processes required sheets using a further in-line function inside the workbook in-line function. It avoids the firewall issue, but at the expense of limiting code re-use (since the in-line functions are inside a query and can't be used by other queries.