Combine multiple Excel files using custom M functions
Vložit
- čas přidán 9. 09. 2024
- This video will show you how you can create a custom M function that is going to clean messy table structure and then use that function to iterate through multiple Excel files, connect to the specific sheets and clean them in the same fashion.
You can download the files and solution on this link:
exceed.hr/wp-c...
👉Need more assistance? Check our website at exceed.hr/en/s...
#powerbi #excel #data #m
From France : brilliant ! Very useful 👏🏼👏🏼👏🏼
Damn, you are very good with at this...
Probably the best I've found so far, when it comes to Power BI!
quite chilly gave a goose bumps..............
Very nice and very helpful Thank very much
What a great idea. Thank you for the video.
This is fantastic and so useful! Thanks for sharing. So So Good!
Super awesome walkthrough!!
Thank you very much from Thailand, very useful tip and trick. I normally have to clean the input files like these and very dynamic method.
Awesome example! Some great tips and tricks there. Thanks for sharing! If there is any way you could share the example file so viewers can follow along with your steps, that would be a plus. Thanks for your videos and thumbs up!!
Hi, thanks for the comment! we've added a link in the description where you can download the files we used in the video.
@@ExceedLearning Awesome!! Many thanks :)) Hope you can do the same with future videos (i.e. provide link to practice files). I learn so much more by being able to follow along hands on vs. just watching. Thanks again!!
Wonderful video 🎉.
Please consider a video giving your take on what’s is the best way to share these custom functions in a team setting, for a team that has novice PQ experience? Can I save these functions in a template document that others can duplicate? How does the “My Organization” feature work? If I import the template using PQ, is that the easiest way for me to have all my pre-built custom functions (toolset) readily available to use?
Also please, how far up can this process be automated? I’m thinking Power Automate to Office Script to run PQ (can it?) or maybe Power Automate to “Dataflows”? 🤔❓
Also please consider extending the videos closing segment a bit so I have time to like and save the video at the end before it switches to the next video. Thank you 🙏 👌🏽.
Thanks for sharing ❤
very usefull. Thanks a lot
Excellent video
this is very useful, thx!
Indeed, this is ingenious. Thanks. Just one thing, please. How would you deal with a situation where the the tables have different number of top rows before the header row?
Hi Sani, I would have used some type of dynamic filtering logic. We also have a blog post about this topic. You can read it here: exceed.hr/blog/dynamically-remove-top-rows-in-power-query
thanks for the great video, I found it very useful! I used this solution to deal with changing column names in my data (transposed first row, made changes, transposed back, and appended the Start table minus 1st row same as you did here pretty much. however, I saw your earlier video you did on dealing with messy column names where you used the dynamic nested lists for the rename function - any suggestion which of the two approaches is better to use?
Awesome
Thanks for the tutorial. I have XL files that are similar to each other but some files have different columns, and different numbers of columns. E.g 2021 file might have 'Staff training' as a column and a range of $ values, but 2022 doesn't have a 'Staff training' column as there was no activity for that year in that as there was no expenditure. Will this function handle different numbers of columns if different source files? Second question if I copy more files to target folder will this function automatically bring the new data in to the resulting table / and to data model.
Thanks again
Hi,
This function might not handle the problem related to the different number of columns, but you can create your own custom M function that will implement the logic you want and solve this specific problem.
If you copy files to the target folder, the function will automatically bring the new data to the data model on each refresh.
Hi, One doubt if 28 Columns or More and need to rename every time. as per requirement. So how can I rename the column in one short. I know that we can change by using -Use first row as header, But if I do next time my file name change so its shows error.
How can I merge 1excel file with another but only 2 colums of it?
Nice tutorial. Could you please post the files and the code so it is easier to practice this technique?
Hi, thanks for the comment! we've added a link in the description where you can download the files we used in the video.