The ‘let’ and ‘in’ combination creates a pseudo custom custom function with no parameters. I’ve been playing around with this recently, it’s a really nice feature which had never occurred to me before.
This is the best tip I ever got. I have 47 Excel files (of assorted xls, xlsm and xlsx extensions...not ideal, I know....) in a folder where I named three ranges Baseline | Claim | Cost (I couldn't Ctrl-T Tables and did not want to go the Sample file in Power BI). So I Unpivoted and copied one Let statement from one query. But instead of "Sheet" I used Source{[Item="Claim",Kind="DefinedName"]} . Then made 2 copies of the same query only changing my "DefinedName" (To "Baseline" and "Cost" in my case). And it worked. Brilliant! Thank you Mark! I will definitely be following you!
Excellent, Mark. Explanation, clear, precise and simple and that will save us a great amount of time in the transformations that must be made to the data for its analysis. Thank you very much for sharing such valuable information with us.
Don’t go throwing out your solution. There are lots of ways to achieve this. In my opinion, it is the easiest to apply, but not necessarily the easiest to debug.
@@ExcelOffTheGrid in my project there are always four files only, but they are always have different location, i need to know only filename, and somehow (i dont remember now) i get full path to each file and get combine their data. But i always look up for any solution, may be i'll find something useful foe my work. So, thank you for sharing your method!😊
A few weeks I needed to do just this. I couldn’t find a video or tutorial about combining files from multiple folders. I eventually made a custom solution but I think it’s long and complex. This is a lot simpler
As I mentioned in the introduction, this is for when the files are disbursed all over the place, with a mixture of file names, sheet names, and file locations. The example uses a simple file structure, because I’m too lazy to create file chaos just so I can ignore it.
Hi, if there is a set of different Excel files and there are data transformation steps applied to each of them them individually and then all the files are further connected with steps like merge, combine and group. Now there is an update in one/all Excel files in terms of data (columns remains same)and it is not possible to update each and every column value, in this case how toreplace the original Excel file/files (keeping the same file name) so that the all the steps can/will be applied as it is after file replacement on the new file and the outcome will be based new files
Very much effective and time saving. Many thanks for posting this video
Glad it helped 😁
Excellent Mark. Thanks
Thanks Ismael 👍
Brilliant tricks to combine various files 👌...
Woow, I really loved that you added all of M codes inside custom column.
Thanks Mark.
The ‘let’ and ‘in’ combination creates a pseudo custom custom function with no parameters.
I’ve been playing around with this recently, it’s a really nice feature which had never occurred to me before.
Exactly what I needed. Thank you!
You're welcome! 👍
So simple and very helpful
Glad to hear that! 😁
I mean wow...this is a special video..great job
Fantastic Mark. Excellent resolution with Power Query. As always contributing light in this matter. Thank you.
Thanks Ivan 😀
Thank you very much!
I hope you can put it to good use.
This is the best tip I ever got. I have 47 Excel files (of assorted xls, xlsm and xlsx extensions...not ideal, I know....) in a folder where I named three ranges Baseline | Claim | Cost (I couldn't Ctrl-T Tables and did not want to go the Sample file in Power BI). So I Unpivoted and copied one Let statement from one query. But instead of "Sheet" I used Source{[Item="Claim",Kind="DefinedName"]} . Then made 2 copies of the same query only changing my "DefinedName" (To "Baseline" and "Cost" in my case). And it worked. Brilliant! Thank you Mark! I will definitely be following you!
Simple solution, perfect! It was exactly what I was trying to for trial balance files!
Good work - hopefully it can save you a bit month end time.
Excellent idea 👍
Glad you think so!
Mind blown! What a great solution - thanks Mark
I’m glad you like it. I can’t believe I’ve not thought of it before. 😀
Bloody clever!
Thanks - I hope you can put it to good use.
Excellent, Mark. Explanation, clear, precise and simple and that will save us a great amount of time in the transformations that must be made to the data for its analysis. Thank you very much for sharing such valuable information with us.
When I stumbled across this technique, I was surprised at how simple it was for dealing with complex situations. I hope you can put it to good use.
Super as always👍. Thanks Mark
Thanks Kebin. I appreciate your support 😁
Cool! I will check my similar solution. Thanks!
Don’t go throwing out your solution.
There are lots of ways to achieve this. In my opinion, it is the easiest to apply, but not necessarily the easiest to debug.
@@ExcelOffTheGrid in my project there are always four files only, but they are always have different location, i need to know only filename, and somehow (i dont remember now) i get full path to each file and get combine their data.
But i always look up for any solution, may be i'll find something useful foe my work. So, thank you for sharing your method!😊
A few weeks I needed to do just this. I couldn’t find a video or tutorial about combining files from multiple folders. I eventually made a custom solution but I think it’s long and complex. This is a lot simpler
I’ve made similar complex solutions in the past. But I discovered this easy method and wanted to share it. I hope you can use it somewhere.
That’s really a cool and useful trick!!
I'm glad you think so. I hope you can put it to good use.
Amazing.
Thank you! Cheers!
Dear Mark, this is awesome as usual, thank you for sharing.👍
Thanks for watching, I’m glad you enjoyed it.
Excellent, thanks
You are welcome Karen . 👍
That's awesome
Thanks 😀
Great video Mark. Is it possible to do a simular thing and combine data from a list of folders if they are all over the server
It’s possible, but the approach would be slightly different because the connectors are different.
ماشاء الله تبارك الله عمل رائع جدا ومشكور جدا جدا وربنا يهديك
Your video was very helpful but, I want to ask you about, the VIEW>DATA Preview option isn't working even though I have Updated the Version.
Why not create the file list using Power Query - no typing, filtered list as needed?
As I mentioned in the introduction, this is for when the files are disbursed all over the place, with a mixture of file names, sheet names, and file locations.
The example uses a simple file structure, because I’m too lazy to create file chaos just so I can ignore it.
Hi, if there is a set of different Excel files and there are data transformation steps applied to each of them them individually and then all the files are further connected with steps like merge, combine and group. Now there is an update in one/all Excel files in terms of data (columns remains same)and it is not possible to update each and every column value, in this case how toreplace the original Excel file/files (keeping the same file name) so that the all the steps can/will be applied as it is after file replacement on the new file and the outcome will be based new files