Want to understand M, the language that powers Power Query? Power Query’s GUI Query Editor is great, but pointing and clicking only gets you so far.
Whether it’s the blank “custom column formula” box that greets you when you add a custom column, or the mass of code revealed by the advanced editor, you find your Power Query work brings you in contact with the M language.
Searching online, you can find M code fragments which you can copy-and-paste. Maybe at the start of your Power Query journey, this was satisfactory—but now you’re ready to go beyond hacking someone else’s code examples (that somehow seem to work) to actually understanding what they do and why they work. Maybe you’re even interested in (or feel the need) to go further—to write your own mashups from scratch so you can make them do precisely what you want. Foundational to both is knowing the M language.
In a nutshell: You already use Power Query to work with data but you’d like to take your data mashing up to the next level. You want to learn, you want to understand M—the language that powers Power Query.
Sound like you? Then join me for this course—join me on the rewarding journey of learning M!
- Monday-Thursday, November 8-11, 2021
10:30 am–1:00 pm Eastern Time (last day may finish earlier)
- Monday-Thursday, November 8-11, 2021
- Limited to about 15 students
- Special introductory price: $800 USD
- Beta course: You’ll be one of the first to see this presentation; your feedback will have the opportunity to help shape it for the future.
- Goal: Enable you to go further with Power Query by providing a sound foundation in the basics of the M programming language and in working with tables using M.
- Training Style: A systematic walk-through of the language (with examples) covering key features of relevance to basic and intermediate-level users, plus an exploration of key table functions (again, with examples).
- Intended Audience:
- Comfortable working in Power Query’s GUI query editor (found in Microsoft tools such as Power BI, Excel and Power Query Online)—but you certainly do not need to be an expert at it.
- No programming background required, but comfortableness in grasping some level of technical concepts would be important.
- Computer with audio and video capabilities (web cam optional).
- Stable Internet connection.
- Microsoft Power BI Desktop or Microsoft Excel 365/2019 installed.
- Microsoft Teams desktop client installed (highly recommended).
- Teams will be used to deliver the course. To ensure a quality experience, the desktop client should be used instead of the web-browser-based version.
- Instructor: Ben Gribaudo, author of the Power Query M Primer series, longtime BI developer, database engineer, programmer and technical architect.
- The Journey Begins
Drinking from the fire hydrant: A crash course in how to read M code.
- Simple Scalars: number, text, date/time/datetime/datetimezone, duration, logical, null
Meet the basic “bits” of M life that make up most values in its universe. While not glamorous, scalars are the base on which more exciting things like lists and tables are built on, so a sound grasp of these values are important.
- Building Blocks: functions, records, let expressions
Weave, reuse and compose code using these constructive components. Functions allow logic to be compartmentalized and reused. Records organize related data. Then there’s the ubiquitous
let, which as we’ll learn is basically a record in disguise.
- Sensational Streamables: lists, tables
A tranquil brook? A rushing river? At the heart of most Power Query activities are things streamed, particularly tables. But what exactly is streaming? Essential to effective M coding is a solid understanding of table, its cousin the list, and the key concept of streaming. (The focus here is understanding list and table as data types in their own right, with the later segment on table standard library functions delving into how to use library functions to shape tabular output.)
- Facing Faults: error handling
When things go wrong, what’s a person to do? M’s error handling is easy to code, but, as you’ll learn, can be tricky to apply in the right place to achieve the desired effect.
- Philosophical Ponderings: query folding, firewall
Beyond the raw beauty of the language, there’s the environment in which it operates, encompassing important concepts such as query folding + expression auto-optimization and the firewall. A deep dive into these realms could fill a course of its own, so this course will suffice itself with just getting our toes wet by presenting the basic rudiments.
- Tables, Tables, Tables Everywhere: handcrafting with table functions
The standard library offers a plethora of table-specific functions. Learn how to work with key players from this team in raw M code, where you have access to their full capabilities unhindered by a user interface.
To sign up, drop me an email.