Combining Non-Identical Sheets - Power Query Function - IM ◄► POSSIBLE

In this tutorial I show you how to combine any number of Worksheets where data is totally different in Size, location and lots of undesirable values.
I will clean, transform a sample sheet as a model. Then convert the query into a function that dynamically finds the desired values. By invoking the custom function we will combine any number of worksheets then visualize the data with pivot Tables, Chart and slicer.
You can download the Exercise File and Follow along by clicking on the link:
www.OfficeInstructor.com/public
You can learn everything about Pivot Tables by watching my FREE course:
Pivot Tables for Beginners- Complete Course with downloadable Files.
• Excel Pivot Tables For...
Please do your part in supporting the OfficeInstructor channel : Like , Share, Comment, Subscribe and Ring the bell.

[@wayneedmondson1065]

Hi Nabil. Absolutely awesome! I've been searching the Internet to learn more about designing custom functions in Power Query. So, this is a very timely video for me. Your explanation is perfect.. easy to follow and understand, as well as being able to work alongside with the sample file. I can't thank you enough for sharing your knowledge and experience.. very much appreciated :)) Thumbs up!!

[@Officeinstructor]

Hi Wayne, I love the dose of motivation you give me with every new tutorial. Thank you so much.

[@wayneedmondson1065]

@@Officeinstructor Thanks Nabil! I appreciate all the excellent instruction :))

[@mcwahaab]

Practical real life example and easy to follow instructions. Thanks a million Nabil.

[@ariannemaedelapena9916]

Ohhh my God! Thanks that I found you! This was I looking for the whole night! Thank you so much!

[@reborngarage1]

You are the best Nabil!!!

[@piersmountford9982]

Absolutely Brilliant!
Clear and concise guidance. Presents so many ideas that I may apply to my other processes.
That's what I enjoy with these applications. So many different ways of creating, managing and displaying data!
I'm always willing to learn new ways (now in my mid 70s) and this has been a great example.
Glad your move was a success

[@syedaneesdurez8766]

All your videos example explained are Amazing. Mashallah

[@houstonsam6163]

Many thanks Nabil, this is the best demonstration of functionalizing a query I have yet seen; the index column to handle varying numbers of rows above the data from each worksheet is fantastic.

[@Officeinstructor]

Glad it was helpful!

[@1qtaz]

So clear and easy to follow! Thanks!

[@Officeinstructor]

Glad it was helpful!

[@iankr]

Brilliant! Many thanks, Nabil.

[@khopkarrohit]

Great job in demonstrating creation of power query function!!

[@Officeinstructor]

Thank you. You are most welcome

[@martyc5674]

Brilliant- clever use of index.

[@Officeinstructor]

Glad you liked it

[@Luciano_mp]

👍👍👍...Brilliant. very useful. Thank you Nabil!

[@Officeinstructor]

Glad you liked it!

[@mmunozidata]

Thank you for your amazing work.

[@Officeinstructor]

Glad you enjoy it!

[@thecreativetime4347]

Very useful and informative 👍..... Also let us know how to do if source data is from folder and not from a single workbook

[@Officeinstructor]

Sure I will
Thanks for the feedback

[@sarajvo2010]

Simple and great, thank you!

[@Officeinstructor]

You're welcome!

[@silverfunnel6819]

This is so useful, thank you!

[@Officeinstructor]

Glad it was helpful!

[@IvanCortinas_ES]

New Nabil video = New jewel

[@GrainneDuggan_Excel]

Interesting solution going to the source folder twice. I like it.

[@Officeinstructor]

Glad you like it!

[@Saad.PS2009]

Thank you so much
Mr Nabil

[@Officeinstructor]

You are most welcome

[@مسلم_سني_مسلم]

Thank you very much

[@FRANKWHITE1996]

again - great video!

[@SamehRSameh]

Marvelous video ,, great

[@Officeinstructor]

Thanks for the visit

[@excelworx8712]

That was a brilliant video, Nabeel. Is there any way we can use our functions in PQ in other files?

[@FRANKWHITE1996]

Great video thanks for sharing

[@Officeinstructor]

Thanks for watching!

[@mohdimranyusof2889]

Good one, but need to go through again and again to understand it.

[@StefanoVerugi]

gold

[@joesardone3801]

Hi Nabil, Thank you for the great video, but there is one thing I don't understand. In your function, there is a line where you filtered [Column14] = "Total", but this isn't always in column 14, so how does this function correctly apply this step when the other years would not have had a "Total" in column 14?

[@joesardone3801]

Nevermind, it looks like the data is always 14 columns wide since power query ignores preceding empty columns.

[@JSUG4219]

Excellent video and explanation. However, in the beginning of the video, you show that each of the year's worksheets have the actual data beginning not just in a different Row, but also a different Column. That means that the "Total" column is not always Column N (14). However, in the sample file and the function you create, you hardcode Column14 for filtering looking for the row with "Total". Can you show us how to modify the function to also make the column dynamic? Thanks!

[@ariannemaedelapena9916]

ff

[@reborngarage1]

Help me to understand what went wrong on mine
When I hit fixing column only one was table the other were error

[@johnborg5419]

I cannot find the files to download, can you help me??

[@julie_chen]

👍👍👍🙏🙏🙏🙏💯💯💯🎉🎉🎉