Session recorded live on Jul 24
The MS Excel Toronto Meetup group was founded in May 2019 and has had monthly sessions since then. Sessions are currently offered for free online, with a different topic presented by an Excel Expert each month. Members are from all over the world, totalling more than 4,000.
ATTEND THE MS EXCEL TORONTO MEETUP GROUP SESSIONS LIVE 👇
Register for free as a member of the group: meetup.com/MSExcelToronto
ACCESS THE EXERCISE FILES shared by the presenter are available to the group members on the Meetup website 👇
Access on the browser (not the mobile app) go to the main menu and select More - Message Boards.
The MS Excel Toronto Meetup group sessions are organized and run by
Celia Alves - linkedin.com/in/celiajordaoalves/
Gráinne Dugan - linkedin.com/in/grainneduggan1/
-----------------------
Keep up with the Excel News by subscribing to Celia’s newsletter: snapreportschamp.com/subscribenewsletter
Learn how to automate your Excel reports:
snapreportschamp.com/course
-------------------------------------
About this session:
Have you ever felt like you're repeating the same data manipulation steps over and over in Power Query? Do you wish there was a better way to automate and standardize that process? You're in luck because you and your peers voted for our topic: Custom Functions!
Custom Functions 101: What are they? Why should you care?
DIY: I’ll show you how to build your own custom functions. Trust me, it’s easier than you think!
Ready-to-Use Goodies: I'll share some of my custom functions. Ready to be implemented into your workflows immediately!
---------------------
About the Speaker: Melissa de Korte
Melissa de Korte is a passionate problem-solver known for her ability to simplify complex problems with Power Query and M. With an impressive track record as an Enterprise DNA Expert and Fabric Community Super User, Melissa has become an asset to the data community, actively engaging, supporting, and inspiring others.
She posts regularly on LinkedIn using the hashtag #pqtodaysmenu and is co-author of "The Definitive Guide to Power Query (M)," a comprehensive resource essential for both beginners and seasoned professionals. And has been awarded the Microsoft MVP (Microsoft Most Valuable Professional) Award for her contributions to the community.
--------------
Find out more and connect with Melissa de Korte:
LinkedIn:  linkedin.com/in/melissa-de-korte-64585884/
The Definitive Guide to Power Query M: amazon.com/Definitive-Guide-Power-Query-Transformation/dp/1835089720
Timestamps
00:00:00 - Celia Alves MS Excel Toronto news and upcoming events
00:10:30 - Celia introduces Melissa de Korte
00:14:00 - Melissa introduces herself and Custom Functions in M
00:19:00 - Introduction to custom function recipe and parameters
00:24:40 - Generate table with no inputs – Refresh Table Function example, including optional parameters
00:36:00 - Option 1: Calculate the difference in days between 2 dates - include or exclude end date or holidays etc.
00:43:25 - Option 2: Calculate the difference in days between 2 dates with optional parameters for including or excluding end dates or holidays etc. (introduces coalesce operator)
00:50:55 – Summary of list, record, use records to create multiple columns in a single step and use Coalesce operator to deal with optional parameters and null values
00:55:00 - Functions to apply to a table, list or record. Prevent data loss: Create a function to split column by delimiter with and unknown number of new columns. Rename the columns incrementally
01:03:40 - Questions: Row detection limits for various built-in functions, default value & optional parameters in split column custom function; best practices for performance; handling errors in custom functions; advice when learning Power Query and M
01:19:10 - Wrap up
- MS Excel Toronto - Power Query Building Solutions with Custom Functions in M - Melissa de Korte ( Download)
- Power Query in the Trenches: 3 Solutions | Melissa de Korte | VanPUG Excel Track-Jun 2022 ( Download)
- MS Excel Toronto Meetup - Power Query Route to M(astery): Challenge Yourself - Melissa de Korte ( Download)
- MS Excel Toronto | Power Query Fundamentals for Excel Reporting Tasks | Celia Alves ( Download)
- MS Excel Toronto Meetup - The Magic of Filters in Power Query - Alejandra Horvath ( Download)
- PowerBI Guru Series E24 - 5 Amazing Things With Power Query with Melissa de Korte ( Download)
- Connect Live with Melissa de Korte ( Download)
- Power Query in the Trenches: 3 Solutions to a Data Problem | Melissa de Korte ( Download)
- Power Query Route to M(astery): Challenge Yourself! | Melissa de Korte Nov 2022 ( Download)
- Microsoft Excel Office Hour 98: Power Query in the Trenches: 3 Solutions to a Data Problem ( Download)
- 3 solutions to a data problem | Melissa de Korte ( Download)
- MS Excel Toronto Meetup - How to build an Excel automation machine - Mark Proctor ( Download)
- MS Excel Toronto - Guerrilla Pivot Tables for Exploratory Data Analysis - Carlos Barboza​ ( Download)
- MS Excel Toronto - Extracting from the Data Model with Cube Functions - Brent Allen ( Download)
- Preppin Data 2022 Week 1 ( Download)
