Use Power Query to Combine Excel Sheets into one table - 3 Methods Easy - Hard

I show 3 techniques for combining / consolidating the data from an Excel file

00:00 Intro
02:21 Connect to a SharePoint file
03:45 The trick to consolidating dynamically
04:23 Consolidate Tables - Difficulty - Easy
06:21 Consolidate Sheets - Difficulty - Medium
10:50 Using a Custom Function if your data is messier - Difficulty - Hard

Download the files I used

Subscribe

Connect with me

[bhavyagupta]

This was really mindblowing stuff 🤯 Thanks for sharings these hacks.

[hk_k]

This is the 1st time I hear about parameter, create function, and evoke. Amazing. Worth much more view and like.

[peterc]

Creating functions for cleaning up your data - very powerful!!! Thanks for the great information

[patrickkinbonso]

Thank you for this video. I have been looking through YouTube and found no other video showing how to combine multiple sheets of a SharePoint Excel file into one table, and your video has just saved me from growing a few grey hair 😂

[IvanCortinas_ES]

Awesome!! The power of parameters! Excellent tips. Thank you Wyn!!!

[shanestocks]

Brilliant stuff. I am working in inventory planning and I am building a "tree" of sorts in SharePoint, with raw data being the roots, and this is useful for me to bring up the raw data into the analysis workbooks without the user having to copy and paste the data every time. Bravo sir!

[ariellepoudel]

Thank you so much! I was really struggling with this and you made it so easy. I watched a few different videos and yours was by far the best!

[RenierWessels]

This was awesome Wyn. Always learning loads from you! Thanks for sharing.

[mohammedelsakally]

Thank you Mr. Hopkins for your valuables informations you always share with your followers ... we are awaiting for a solution about how to consolidate a specific kind of data from multiple different workbooks containing different worksheets containing different headers and these data are not included in named range or tables ... how we can doing this by power query? ... i know you are prepared many videos around this solutions... but there are a problem appearing when refresh this data especially if collected from a different users ... thank you again and all respect for you..

[heshamfouad]

Sadly I can only Give one Thumbs up,
as usual Super Video, I've grown a habit of watching your videos even if I already know how to do the task, as I always learn one or two (This time 3) tricks from your videos

Thanks and keep going 🙂

[iduncanw]

Great video Wyn. I'm just starting to expand my Power Query knowledge so definitely picked up some tips. A lot of the stuff I work with, I often have some control over the data sources. The more I'm learning, the better I can design the data sources. Making some good use of getting data from folder to consolidate like data, either from multiple users or history of files.

[joseagundis]

very good, excellent video, Wyn regards.

[GeertDelmulle]

Hey Wyn, nice video as always. Here are my thoughts:
• I always avoid linking to SharePoint by making a local copy first. Perhaps I should try to venture there sometimes. :-)
• I was expecting Excel.CurrentWorkbook, but you carefully circumnavigated that one. ;-)
• working and playing around with those Boolean arguments of Excel.Workbook function, we’ve know for quite a few years now, and so do you (I’m sure of it) — I myself have learned it from excelisfun. Sometimes you want that, but with variable headers you don’t, IMO.
• when cleaning up those complex headers you can skip the top lines until you reach the true headers in a dynamic way using a custom function as an argument to that skip function. I like the flexibility of that.
• I have neutralised that automatic Change Type behaviour quite a long time ago (and the default load behaviour for me is Connection Only).

[manasa]

Thanks, Wynn, helpful to do so.

I am also looking to separate the city consolidation sheet into different sheets / files with same format, formulas intact.
For eg. Split the file based on one column viz. Customer.
Can it be possible without VBA.?

[DeasyPutra]

When you create a custom function, you referred to FnDemo A only but it seems to apply across all FnDemo A, B to C. Does that mean the formula will go across 3 sheets that you consolidated as it is now formulated under a PQ formula function? maybe i missed some steps but couldnt figure it out with replays. TIA

[mohamedsoffar]

Thank you Dear For your priceless contents, I really appreciate your efforts in This, Many Thanks To You Coming From Egypt🤩

[shanestocks]

All works brilliantly however having a little issue, when I add a column at the raw data and refresh the workbook that is linked, the new column does not add? Is this a more advanced approach? The raw data is in range format.

[wken]

Very good video Wyn. I've learned many of these Power Query tricks but like the Promote Headers hack and appreciate your advice on creating functions. I'll also use Reference more in the future. Thanks!

[TheJoshtheboss]

What if I have 2 tables similar data. What I mean is that each table could have 6 columns for instance. But the common data between them is only 3 columns, the other 3 are unique to them which I don't want to combine.

I assume Power Query merge can do it, or Data Model relationship.

[sharonpruner]

What do you do if tables you want to combine are in different folders in SharePoint?