Foreword | Crunching Data for Absolute Beginners: Learn to Code with Microsoft M-Language for Excel

8 min readMar 29, 2018

I started my professional career as a copywriter at an advertising agency. Later I became a marketing professional and in total, I spent working more than two decades for huge, multinational corporations and well-pampered startups (with up to MM300 USD in equity funding). Nothing indicated that I would eventually teach myself to code, and the beginnings were pretty hard. Not only because coding is … just hard, but I also had no plan whatsoever:

What language should I learn first?
How can I apply my knowledge at work?
How far can I get with coding, given my background?

In the beginning, my learning journey was mostly fumbling, stumbling, some blissful moments and loads of frustrations. The purpose of my course is to show you a viable path to coding with less pain and more gain than I encountered along the way.

This is my journey to programming. What is yours?

I assume that my story might be of interest for anyone with a similar background to mine:

you have no formal programming education
you work in the line of business at a corporation
you like to get things done smartly

Oh, by the way. Your age doesn’t matter. Whether you are in your 20s, 30s, 40s … 50s, 60 or even 70s. It just does not matter. I am in my late 40s. Admittedly, acquiring new skills is becoming increasingly difficult as you get older. But it’s feasable at any age, even if your brain is not wired for programming just as mine wasn’t in the beginning. I am at ease with natural languages, and I was under the impression that programming, math … the geek stuff in general is for people coming from a different planet. But as I kept trying and going, I noticed how my brain started to rewire itself towards coding, and now I truly enjoy getting better at crunching data, creating reports and sharing my learnings with likeminded people.

Why Excel? Why Microsoft M-Language? What’s the point?

I learned some HTML5 in conjunction with CSS, and I managed to build basic websites with Bootstrap. I also learned Python, and as a result I have a basic understanding of a object oriented programming language.

I highly encourage you to learn Python or R if you want to become good at crunching data. Knowing the basics of HTML5 & CSS does not hurt either. But neither should be your first language. Microsoft M-Language is a better choice to get started and then move on to the forementioned languages.

Microsoft M-Language and Excel is the ideal entry drug into coding

Microsoft Excel has a user base of approximately 750 million worldwide. It’s a ubiquitous technlogy in the corporate world, and it comes with a set of four languages built for different purposes:

Microsoft Excel formula language allows you to perform simple computations on cells, such as: “Compare the values on cell A1 and B1 and show the difference on cell C1, based on specified criteria.” … up to complex financial modeling. Coming from Google Sheets (which I still keep using), I was amazed how much more you can do with Excel formula language. YouTube is a great resource to learn Excel, and my favorite channel is ExcellsFun by Mike Girvin with more than 3,000 Excel videos and over 100 million views.
Microsoft DAX (Data Analysis Expressions) goes a huge step further, as it enables you to perform calculations on entire tables: “Grab the revenue column from table A, substract COGS in table B and display the resulting gross margin in a new column on table C.” I am starting to learn DAX at the time of writing this series, and the best resource on this subject I found so far is “The Definitive Guide to DAX” by Marco Russo and Alberto Ferrari.
Microsoft M-Language is a “powerful query language optimized for building queries that mashup data” as Microsoft puts it. If that’s sounds cryptic to you, let me reframe it in layman’s terms: M-Language helps you easily clean up huge, messy and incompatible data sets, and thus ramp them up for advanced analytics. More importantly, M-Language allows you to automate that process, which saves you tons of time and helps avoid mistakes. My favorite book on this subject is “M is for (DATA) Monkey” by Ken Plus and Miguel Escobar.
Microsoft VBA (Visual Basic for Applications) is for application development as its name indicates. I cannot say much about VBA as I only have played around a bit with VBA based on some YouTube tutorials.

You can jump into M-Language right away, even if your knowledge around Excel is limited to just opening a Workbook and summing up values.

Acquire the unsexy, yet highly useful skill of cleaning up data with M-Language

There is a lot of buzz around data analytics & science, and virtually any job in the corporate world exposes you to a growing number of data that you somehow have to juggle with, analyze it and then present the findings to your team and superiors.

Cleaning up data does not sound appealing at first glance. But imagine the following scenario: You want to create a competitive analysis, and you get data in various formats like: Excel, CSV, JSON … SQL, additionally you scrape some data from the web, and you somehow have to consolidate that messy pile of data … M-Language will do exactly that. It will make your data look clean and neat so that you can actually work with it.

Learning M-Language is like learning to ride a bicycle with training wheels. It’s impossible to fail

It is actually not mandatory to learn to code in order to make use of M-Language. There are only few cases where you actually have to write code in M-Language. You can do most of the stuff through the UI (User Interface), the code will be autogenerated for you.

However, if:

you are a curious person
you want to grasp how things work under the hood
you are keen on understanding the most fundamental concepts of programming
you want to acquire skills which are in high demand

… this course is the right place for you. If you don’t care about what’s happening under the hood, skip this course and go straight to “M is for (DATA) Monkey”. You can always come back to this course later.

I personally work with the M-Language UI most of the time, and I use my M-Language skills to read and tweak my code in order to increase the level of automation. I read a lot of M-Language code, but I do not write much by myself. Which reminds me of learning a natural language: You learn faster to read than to write. There are not many programming languages that allow you to do so.

What exactly are you going to learn in my course?

I will structure my lessons in six chapters:

#1 Pick your environment: Microsoft Excel or Power BI
We’ll start off by selecting the right environment for you. Depending on your Office 365 subscription plan, M-Language is already built in and available in Excel. If that’s not the case, I will show you how to get Microsoft Power BI installed on your desktop. You should be able to follow this course when using Power BI, M-Language works similarly in both products. I might create a separate learning track for Power BI users further down the road, depending on the feedback I get on this series here.

#2 Write your first “Hello World!” program
Almost every programming course targeted at absolute beginners starts with writing “Hello World!”, so does mine. In this first chapter, you will learn about some foundational conventions of M-Language.

#3 Working with primitive values
Primitive values can be for example:

whole numbers like 1, 2, 3, 4, 5 etc.
decimal numbers such as 3.14159
or texts such as “Hello World!”

You will learn about different types of data and how M-Language treats them.

#4 Working with structured values
Structured values are composed of primitive values. For example lists. This list is composed of multiple text values.

{“apples”, “oranges”, “bananas”}

Or records: “How many apples, oranges and bananas do you currently have on stock?”

[ apples = 5, oranges = 7, bananas = 10]

There is a total of 15 different primitive and structured values, and you will get an introductory overview over those I consider most useful to absolute beginners.

#5 Real world project: Mashing up data
We will simulate a real life scenario by consolidating different types of data into one unique format. Most of the work will be done through the UI, and we will then look into the autogenerated code to gain a better understanding of what’s happening under the hood.

#6 Real world project: Pull & clean up data from meetup.com via API
Don’t feel intimidated by the terminology. Back a few months ago, I thought that working with an API (Application Programming Interface) is reserved for real developers, not for self-educated marketing guys like me. Nothing could be further from the truth. With Excel, you can access data from a 3rd party through their REST APIs and clean it up with help of M-Language.

I am launching this series of blogpost as an MVP (Minimum Viable Product). However, I am contemplating to create accompanying video tutorials, depending on my readers’ needs, request and feedback. You can reach out to me at any time if you have suggestions for further tutorials or questions around the current content. Bear in mind, I am just like you. Someone who loves to learn, the only difference is that I am a few steps ahead of you on my journey to become a data savvy professional.

Let’s get started!