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.
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/
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.
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 UA-cam channel. It seems like channels with high views tend to have lots of featured other channels. I wonder if UA-cam'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 UA-cam! We are still fresh in this area, so every feedback is much appreciated! Cheers
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!
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!
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().
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)?
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
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"} )
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.
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.
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?
Best thing I’ve watched ever on Excel data Manipulation!! Thanks for sharing this knowledge!! Let me follow you INSTANTLY!! ❤️❤️❤️❤️
I love you man!! The level of simplicity in teaching and amount of orderliness in your thinking is a gift
This one is cool!!
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.
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/
Wow! I know I will feel competent using functions now. Thanks so much.
Man, amazing. It blows my mind. Thank you so much . It saves ton of working hours. 6 stars
Absolutely Brilliant.
Thanks for Sharing. 👍
Fascinating! I have never understood custom functions, but I'm starting to understand their usefulness thanks to your videos.
Happy to hear that! :)
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.
This video is the best i've ever seen. Helps me a lot. Thank you so much!!
I think your PQ videos are amazing. I have learnt so much from you thanks
Thank you!!!! This is a huge time-saver
I'm officially a fan :) Thanks again!
Thankyou, very useful and very well presented. One of the best Power Query videos i have seen
Congratulations, sir! Always great content!
Thanks for sharing, it's very useful for me. i have learned something on query.
Thanks, this is crystal clear for me to understand each point of code. Waiting for more videos to learn more :)
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 UA-cam channel. It seems like channels with high views tend to have lots of featured other channels. I wonder if UA-cam'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 UA-cam! We are still fresh in this area, so every feedback is much appreciated! Cheers
Excellent tutorial❤
Great video!
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!
Good video. Interesting approach, that I used in a today's task :) Thanks
Glad to help!
Thanks for sharing ❤
Thank you, I was able to use this but with CSV files. Great content
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!
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().
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)?
Did you find a solution?
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
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"} )
Really helping
Sir, Highly beneficial script. Pls share the script . Thanks in adv
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.
@@Mister_Bates Did you find a better solution?
Excel.Objects keeps giving me table and sheet. Then I get an error when I invoke the custom function on the table.
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.
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?
Did you find a solution?
Can you do this with PDF? its not working