Combine multiple Excel files using custom M functions

Рет қаралды 3,490

Жыл бұрын

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-content/uploads/...
👉Need more assistance? Check our website at exceed.hr/en/services/
#powerbi #excel #data #m

Пікірлер: 26

@boissierepascal5755 Жыл бұрын

From France : brilliant ! Very useful 👏🏼👏🏼👏🏼

@navedsaiyed9881 Жыл бұрын

quite chilly gave a goose bumps..............

@karimallahwala7022 Жыл бұрын

Very nice and very helpful Thank very much

@KhajonsakLun Жыл бұрын

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.

@crazyrabbit_ Жыл бұрын

Damn, you are very good with at this... Probably the best I've found so far, when it comes to Power BI!

@amal2503 3 ай бұрын

very usefull. Thanks a lot

@FRANKWHITE1996 Жыл бұрын

Thanks for sharing ❤

@Kenmcfarland001 Жыл бұрын

Excellent video

@controlsgirl Жыл бұрын

Super awesome walkthrough!!

@m-forrest 5 ай бұрын

this is very useful, thx!

@andrewsinha2785 Жыл бұрын

This is fantastic and so useful! Thanks for sharing. So So Good!

@cjimmer4877 Жыл бұрын

What a great idea. Thank you for the video.

@udayteja6595 Жыл бұрын

Awesome

@malchicken Жыл бұрын

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 🙏 👌🏽.

@wayneedmondson1065 Жыл бұрын

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!!

@ExceedLearning Жыл бұрын

Hi, thanks for the comment! we've added a link in the description where you can download the files we used in the video.

@wayneedmondson1065 Жыл бұрын

@@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!!

@SaniGarba Жыл бұрын

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?

@ExceedLearning Жыл бұрын

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

@omalinsky Жыл бұрын

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?

@user-ce7oj5yt3j 7 ай бұрын

How can I merge 1excel file with another but only 2 colums of it?

@prashantadhiakri2964 Жыл бұрын

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.

@anthonyroberts7751 Жыл бұрын

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

@ExceedLearning Жыл бұрын

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.