Power Query M Code Custom Function to Convert 12 Cross Tabulated Data Sheets to PivotTable. EMT 1621

Learn how to take 12 Excel Worksheets with cross tabulated tables and convert them to a proper data set and then a PivotTable Report to show the amount of change and 5 Change for each Cow at the Farm from the Morning Shift to the Evening Shift. Many Power Query M Code tips in the video, including how to create an M Code Custom Function to convert each Cross Tabulated Sheet Data to One Single Proper Data Set. Software Train at YouTube suggest a new way to invoke Excel.Workbook.
1. (00:01) Introduction
2. (01:20) Bring File Path Address into Power Query and name it “DataInProperDataSet”.
3. (02:50) File.Contents M Code Function, as a new step, to Invoke Excel.Workbook M Code Function so we can get the objects in the Excel File. This trick comes from . Software Train at YouTube.
4. (03:39) Look at arguments of Excel.Workbook Power Query Function (M Code Function)
5. (04:09) Filter out unwanted sheets.
6. (04:40) Close and Load DataInProperDataSet Query as Connection Only.
7. (04:53) Duplicate Query, Extract a single Sheet and make transformation to convert Cross Tab Sheet Data to a Proper Data Set. This step is where we create the code that can Unpivot a single cross tab table into a Proper Data Set.
8. (05:57) Promote Headers.
9. (06:09) Filter Total Rows Out.
10. (06:30) Create Conditional Column to create new column with Cow Names.
11. (07:10) Fill Cow Names Down.
12. (07:15) See the Table.UnpivotOtherColumns Function to consolidate all dates (columns headers) and Cow Milk Amounts into Two New Columns.
13. (08:11) Filter out blank rows.
14. (08:21) Change Data Types.
15. (08:30) Load “BuildFunction” query as Connection Only.
16. (08:37) Duplicate “BuildFunction”, and add Custom Function Syntax to Convert the Query to a Re-usable Function. We use the Advanced Editor to edit the M Code.
17. (10:06) Define Power Query M Code Custom Function: 1) Variables in Parenthesis, 2) Go To Operator, 3) Mapping of the Variable.
18. (10:28) Invoke Custom Function in the “DataInProperDataSet” function to convert 12 Cross Tab Tables into 12 Proper Data Sets.
19. (11:00) Append all 12 Proper Data sets into a single Proper Data Set using Table Lookup Operators and the Table.Combine Function.
20. (11:43) Change Load Connection to Load Single Proper Data Set to the PivotTable Cache.
21. (12:04) Learn how to Manually Edit the Custom Function when we determine that there has been an error in the M Code for our Custom Function.
22. (12:52) Learn about fundamentals of a let statement and M Code.
23. (13:46) Open Advanced Editor for an M Code Power Query Custom Function.
24. (14:15) Build PivotTable Report that uses Show Values As Difference From and Show Values As % Difference From.
25. (15:02) Summary

[excelisfun]

Epic Topics:
1. (00:01) Introduction
2. (01:20) Bring File Path Address into Power Query and name it “DataInProperDataSet”.
3. (02:50) File.Contents M Code Function, as a new step, to Invoke Excel.Workbook M Code Function so we can get the objects in the Excel File. This trick comes from . Software Train at YouTube.
4. (03:39) Look at arguments of Excel.Workbook Power Query Function (M Code Function)
5. (04:09) Filter out unwanted sheets.
6. (04:40) Close and Load DataInProperDataSet Query as Connection Only.
7. (04:53) Duplicate Query, Extract a single Sheet and make transformation to convert Cross Tab Sheet Data to a Proper Data Set. This step is where we create the code that can Unpivot a single cross tab table into a Proper Data Set.
8. (05:57) Promote Headers.
9. (06:09) Filter Total Rows Out.
10. (06:30) Create Conditional Column to create new column with Cow Names.
11. (07:10) Fill Cow Names Down.
12. (07:15) See the Table.UnpivotOtherColumns Function to consolidate all dates (columns headers) and Cow Milk Amounts into Two New Columns.
13. (08:11) Filter out blank rows.
14. (08:21) Change Data Types.
15. (08:30) Load “BuildFunction” query as Connection Only.
16. (08:37) Duplicate “BuildFunction”, and add Custom Function Syntax to Convert the Query to a Re-usable Function. We use the Advanced Editor to edit the M Code.
17. (10:06) Define Power Query M Code Custom Function: 1) Variables in Parenthesis, 2) Go To Operator, 3) Mapping of the Variable.
18. (10:28) Invoke Custom Function in the “DataInProperDataSet” function to convert 12 Cross Tab Tables into 12 Proper Data Sets.
19. (11:00) Append all 12 Proper Data sets into a single Proper Data Set using Table Lookup Operators and the Table.Combine Function.
20. (11:43) Change Load Connection to Load Single Proper Data Set to the PivotTable Cache.
21. (12:04) Learn how to Manually Edit the Custom Function when we determine that there has been an error in the M Code for our Custom Function.
22. (12:52) Learn about fundamentals of a let statement and M Code.
23. (13:46) Open Advanced Editor for an M Code Power Query Custom Function.
24. (14:15) Build PivotTable Report that uses Show Values As Difference From and Show Values As % Difference From.
25. (15:02) Summary

[markhenderson]

I really like your style of teaching. Great animation and crystal clear. Thank you for all of your Excel Magic Tricks (Power BI).

[ndjanardhan]

In every video some new tips and tricks to learn form You. I am waiting to your video everyday to learn whats new thing you bring to us. Thank for the video. God blessed you Sir. 😀😀😀❤❤🤗🤗🤗

[janithrukshan]

Well done Mike and thanks for Software Train as well!

[wayneedmondson]

Hi Mike. Great transformation and custom function example. Love the cow data theme :)) Thumbs up!!

[harishpaldhir]

Sir u r a jewel in the crown of the king😈😈😈

A real mvp for students like us

[GeertDelmulle]

Holy cow! Yet another great video with many great tricks!
This is truly PQ-M poetry in motion: Bill’s the poet and Mike adds the motion (and narrative, ...)
Thanks Mike and Bill. :-)

[SyedMuzammilMahasanShahi]

EXCELlent video as usual. Loving it. Thanks Mike for the fun learning.

[roywilson]

Another great video. Lot of stuff covered there. Think I will watch this a few times. Will be vry useful as a springboard for ideas to cleanse out datasets :)

[katerina]

Thank you Mike, absolutely beautiful, I will be busy with Power Pivot during the holidays, good fun. Have a great day Mike 🤗

[brianxyz]

Another great example showing that when it comes to transforming data there's probably nothing Power Query can't do.

[duydiep]

Always learn from your videos! Thanks Mike!

[chrism]

This was great Mike, thank you for continuing to publish all these great videos!

[MichaelOrtenberg]

Wow! My head is spinning from that one!

[mohamedchakroun]

Very Impessive Mike, I appreciate that you are grateful to Excel Teammate :-) You teach us Excel & Ethics :-)

[spilledgraphics]

TOO MIND BLOWING wow... M code & Power Query are amazing!

[ThermalWarrior]

Great video Mike, love learning about power query, and your videos make learning easy.

[robrayborn]

Amazing as always. You’re instruction over the years has helped me so much at my job. Thank you!

[JSUG]

Once again, mastery by Mike, with a little help from Bill, the Poet!

[wmfexcel]

Thanks Mike. Every time I watch your video, I learn something new... Something I thought I knew before... haha... Old sayings, the more I learn, the less I know. :P


On the other hand, as a fan of UI, I prefer to use "Drilldown" to convert the file path to text. Easier for me as a newbie to Power Query. But it's really nice to learn another approach.