MSPTDA 36: How To Build Sample File Parameter For A Custom Function to Transform 12 Cross Tab Tables

In this video learn about how to create a custom Function to transform 12 Cross Tabulated Tables in the Current Workbook to a single Proper Data Set and a PivotTable Report. This video answers the question: How to build Sample File Parameter for a Custom Function.
Topics:
1. (00:01) Introduction
2. (01:38) Get Sheets into Power Query.
3. (04:20) Create Sample Sheet
4. (05:00) Create Parameter
5. (06:45) Create Query with Function M Code Based on Parameter. Lots of Transformation Steps.
6. (10:06) Create Custom Function
7. (10:37) Use Custom Function and Finish Final Query.
8. (11:35) Create Report
9. (11:52) Edit Custom Function
10. (12:50) Summary
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

[Xcwizard]

I wish you all the best this holiday season and throughout the year, Merry Christmas!

Mike, another great technic, again and again, thank a lot.
I have a bit easier way to create a Parameter.

1. A01 Create Binary Query from



2. B02 Create New Parameter Type: Binary Default & Current Value A01

3. Advanced Editor to edit B02
Change from Type="Binary" to Type="Any"

4. Add step to A01 =

5. (06:45) Create Query with Function M Code Based on Parameter. Lots of Transformation Steps.

[dmd]

This is singularly the most useful Power Query video I've seen in 2022. Thank you!

[excelisfun]

Topics:
1. (00:01) Introduction
2. (01:38) Get Sheets into Power Query.
3. (04:20) Create Sample Sheet
4. (05:00) Create Parameter
5. (06:45) Create Query with Function M Code Based on Parameter. Lots of Transformation Steps.
6. (10:06) Create Custom Function
7. (10:37) Use Custom Function and Finish Final Query.
8. (11:35) Create Report
9. (11:52) Edit Custom Function
10. (12:50) Summary

[santiagovillamoreno]

Thanks a lot Mike!! I've been studying Power Query for a while and this is the first time I see something like this. Amazing trick! Exactly what I needed

[waverider]

Love your videos. This is yet another masterpiece showing us creative ways to solve problems. Your tip about going into the options to change the settings so it doesn't automatically change types is a big win for me! You are helping me out, one query at a time. Definitely a devoted follower of your videos!

[katerina]

Thank you for another amazing video Mike, Have a great day Mike 🤗

[shubhampawar]

I've completed your basic excel, advance excel, E- DAB and MSPTDA . In this era no one is going to teach you such depth knowledge in free of cost . You are most generous and kind person I've ever seen . Thanks a lot for everything . I am going to go through MSPTDA again for revision and For more understanding . Godess of knowledge "MAA Saraswati " bless you with knowledge and everything which u want. Please provide me guidance if any in the field of data analysis. Love from India❤️❤️❤️❤️❤️

[zahoorsarbandi]

Truly speaking, you are the BEST. There is not a single person in the world who is such an expert and giving free education!!! A big Salute to your efforts and spreading of free education... May Allah bless you with His kindness and give you more strength.

[SyedMuzammilMahasanShahi]

This is EXCELlent. Thanks Mike for this amazing video.

[mohamedchakroun]

Automation leads to avoid manuallay mistakes :-) Thanks for all Mike you are the best :-)

[davidpellatt]

I’m going to have to view this again a couple of times because there’s some amazing techniques that I’ve just seen. Hope you and your family have a wonderful Xmas and thank you for all the brilliance you’ve shared with us this Year

[shipingli]

Thank you, Mike. And Merry Christmas.

[MalinaC]

Thanks for sharing. The number of techniques I want to learn still grows :). Thanks a lot, Mike!

[aminshahin]

Thanks a lot for the video and wish you and your family a merry Christmas and happy holidays👍👍

[wayneedmondson]

Great one Mike! Thanks for the M code wizardry with parameters and custom functions. Thumbs up!!

[enriquedominguez]

Kudos for you and for Bill, Mike. Simply amazing.

[johnborg]

Thanks mike. Alot to absorb in one view :) :)

[shubhampawar]

You are unmatchable and genuious teacher ❤️❤️💯💯😊👌

[ajayganeshie]

This is great work man! Thnx tot the lesson.

[ndjanardhan]

Great, Helpful & Time Saving Video, Thanks a lots Sir for the saving time of us. God Bless You. Thank You Very Much. 😀😀😀❤❤.