Very nice series! I have done this with the query from folder > combine & edit wizard feature but this way is cleaner, more powerful and you have even more control. I have to try to apply this to the last folder I'm using... THX!
Wonderful! Much appreciated :) A few questions this brings up for me: 1.) is there a function you could make that doesn’t take away the top two lines of the original script, so you put in the *file locations* as the variable rather than the table within that file. I think that’s what the power query ‘Get data > folder’ method does? 2.) could the new script which uses the FxClean function itself now be made into a function that could be applied to different folders with similar file structures within it, as the above examples? A custom function within a custom function type thing? 3.) Is there a way thru Power Automate to invoke these power query functions / actions. I’d like to automate these data cleanings being performed, so I’m just entering the data and the next morning all my data has been “power automate calling power query functions automated” into the structures I need, saved to the different locations I need, emails / teams message to the relevant people, auto marking itself off as a done task, etc. I guess it’s a question of how far with the task automation can one go?
Hi Hendrick, you can find the answers below: 1) Yes, this can be achieved by adding a text type of function parameter (something like (FolderLocation as text) => ), to make the folder path dynamic. Although this is possible, there is an option to use the Power Query parameter which is the better solution in case you want to make your folder path dynamic. 2) Excellent idea, and very much possible! You could use the FolderLocation as a text variable as shown in the answer above to create a function. Then, you would need to create a table with all folder paths that you want to process with function. After this step, you can invoke the function on the column that contains folder paths :) 3) You could use the Power Automate to save files in a folder, and on each refresh, the Power Query script will automatically process all the files in the folder (including the one you added with Power Automate).
@@ExceedLearning 1.) great, hmm, I’ll have to try that. The difference and advantage between calling a parameter vs just calling another query eludes me a bit but...I likely just need to play with functions more to get it. 2.) Wonderful! I’ll have to try this out as well. 3.) hmm, yes, makes sense. Next step, I’ll want to look into if there’s a way to auto-refresh my power query. Maybe that’s already an option I just need to find it ^^ . Thank you 🙏
@@malchicken in case you use Dataflows or PBI model in web service, then there is an easy to use auto-refresh feature. In case you are using Excel, then there is an option of scheduling autorefresh of a certain table created with PQ, but it only works in case your file is opened. VBA solution also works only in an open Excel. I believe currently the only option to invoke autorefresh of a closed Excel file is through a shell script.
Excellent tutorial, thank you
This playlist of three videos helped me a lot.
I have been trying to solve this for three days, and finally, I found your vide.
thanx man :)
I hope to continue this tutorial
Informative...
Very nice series!
I have done this with the query from folder > combine & edit wizard feature but this way is cleaner, more powerful and you have even more control. I have to try to apply this to the last folder I'm using... THX!
Thank you Nuno! glad you enjoyed our video and found it useful!
Wonderful! Much appreciated :) A few questions this brings up for me: 1.) is there a function you could make that doesn’t take away the top two lines of the original script, so you put in the *file locations* as the variable rather than the table within that file. I think that’s what the power query ‘Get data > folder’ method does? 2.) could the new script which uses the FxClean function itself now be made into a function that could be applied to different folders with similar file structures within it, as the above examples? A custom function within a custom function type thing? 3.) Is there a way thru Power Automate to invoke these power query functions / actions. I’d like to automate these data cleanings being performed, so I’m just entering the data and the next morning all my data has been “power automate calling power query functions automated” into the structures I need, saved to the different locations I need, emails / teams message to the relevant people, auto marking itself off as a done task, etc. I guess it’s a question of how far with the task automation can one go?
Hi Hendrick, you can find the answers below:
1) Yes, this can be achieved by adding a text type of function parameter (something like (FolderLocation as text) => ), to make the folder path dynamic. Although this is possible, there is an option to use the Power Query parameter which is the better solution in case you want to make your folder path dynamic.
2) Excellent idea, and very much possible! You could use the FolderLocation as a text variable as shown in the answer above to create a function. Then, you would need to create a table with all folder paths that you want to process with function. After this step, you can invoke the function on the column that contains folder paths :)
3) You could use the Power Automate to save files in a folder, and on each refresh, the Power Query script will automatically process all the files in the folder (including the one you added with Power Automate).
@@ExceedLearning 1.) great, hmm, I’ll have to try that. The difference and advantage between calling a parameter vs just calling another query eludes me a bit but...I likely just need to play with functions more to get it. 2.) Wonderful! I’ll have to try this out as well. 3.) hmm, yes, makes sense. Next step, I’ll want to look into if there’s a way to auto-refresh my power query. Maybe that’s already an option I just need to find it ^^ . Thank you 🙏
@@malchicken in case you use Dataflows or PBI model in web service, then there is an easy to use auto-refresh feature. In case you are using Excel, then there is an option of scheduling autorefresh of a certain table created with PQ, but it only works in case your file is opened. VBA solution also works only in an open Excel. I believe currently the only option to invoke autorefresh of a closed Excel file is through a shell script.