#5 Lesson — Part 1 | Learn to Code with Microsoft M-Language: Real World Project — Mashing up Data

9 min readMar 29, 2018

In the previous lesson, we created multiple structured values: a list, a record and a table. It’s not an uncommon scenario that you are receiving data from multiple people in various formats. Let’s imagine, you are tasked with analyzing customer feedback and you get data similar to the one you created in the previous lesson:

person A sends you a list of all countries your company is catering to
person B provides records from customer calls
person C gives you a translation of your customers’ feedback

Before we get started, let me show you what we are about to create in this lesson. We are going to consolidate the data into a single Excel Table.

Now, that might not sound like a lot. We are dealing with a small amount of data here, which we created off hand in the previous lesson. However, imagine if you had thousands or even hundreds of thousands of customer records. That’s where M-Language shines.

Like I said in the very beginning of this course: cleaning up data does not sound like an overly sexy skill to acquire. However, data is useless, until it’s cleaned up and consolidated. You can’t perform any meaningful analysis on dirty & messy data. That said, the skills you are acquiring here might not be glamorous, but of tremendous value for the company you are working for.

Now, let’s get started and get our hands dirty!

First, open the SupportData Excel file you created in the previous lesson. It should look like this.

Click on Enable Content. Expand the Data Ribbon and select Queries & Connections.

Your Excel Workbook should look like this.

When you move your mouse over the list, record and table, you will see their previews. You will notice an icon indicating what type of data we’re dealing with.

A quick refresher: Rember when we worked with primitive values? In our previous lesson, we wanted to create a unique customer ID consisting of the first letters of the customer and a unique number.

let
    FirstName = "P",
    LastName = "S",
    Customer = 1000,
    ID = FirstName & LastName & Customer
in
    ID

This piece of code gave us an error message. Why? Because you can’t mash up different types of values like text and whole numbers. You have to convert a whole number into a text value, and then you can concatenate it with the remaining the text values.

let
    FirstName = "P",
    LastName = "S",
    Customer = 1000,
    ID = FirstName & LastName & Text.From(Customer)
in
    ID

The same goes with structured values. You can’t just grab a list, a record and a table and consolidate them into one table for example. First, you have to convert the list and record into tables. Once you’ve done this, you can merge all three tables into one. Let’s go ahead and do that step by step.

Right click the CountyList and select Edit.

On theTransform Ribbon hit the To Table icon.

A new window will pop up asking to select a delimiter and how to handle extra columns. Leave the default setting as they are and hit OK.

You just converted a list into a table. Congrats! Now, go ahead and double click on the default table header name Column1 and change it to Countries, then hit enter. Are we ready to consolidate that newly created table with the other pieces of data? Not yet!

We have to create an Index Column, by which we are going to merge all three tables. Go to the Add Column Ribbon and click on the down arrow of the Index Column icon (this command, like many others, consits of two parts). Select From 0 and presto, you got an Index Column. Leave it’s name as it is, and let’s move on to our records.

Under Queries select SupportCallRecord. Once it’s highlited in green, you’ll see the record’s content displayed.

On the Convert Ribbon select Into Table and do the exact same steps as before when converting your list into a table. Next, add an Index Column.

Last but not least, let’s go ahead and add an Index Column to the TranslationTable (make sure you select From 0). You don’t need to convert Into Table as this already is a table.

Now, let’s merge those three tables.

On the left hand side select the CountryList table (it should be highlighted in green). Next, select the Home Tab, go to the Merge Queries icon and click on the down arrow right to it. Select Merge Queries as New. The description underneath Merge reads: “Select tables and matching columns to create a merged table.” That’s what we are going to do in two steps.

From the drop-down list select SupportCallRecord. That way, we select the tables which we want to merge.

Next, we have to select the matching columns by which we are going to merge our two tables. Click on the Index Column name of your CountryList. Next, click on the Index Column name of your SupportCallRecord table. Leave the Join Kind default setting as is.

Before you hit OK, let’s take a look at the message next to the green checkmark: “The selection has matched 3 out of the first 3 rows. As you can see, the index columns are matching perfectly. It’s very much like putting together IKEA furniture. Remeber those cute little walk throughs?

The exact same logic applies here. Figuratively speaking, we are putting together Billy shelve type of tables. Obviously, each element has to have a corresponding counterpart which looks exactly the same. What happens, if you try to match the CountryList’s Index Column with the SupportCallRecord’s Customer Name Column? You’ll get an ungly error message: “Select columns of the same type (number, text, etc.) to continue.”

Let’s try to match the Countries column with the Customer Name column. This time, you will be kindly informed that: “The selection has matched 0 out of the first 3 rows.”

Long story short, we’ve appended an identical Index Column to each of our tables to make sure we can “glue” them together into one table. Very much like like a Billy shelf. Let’s go back to the original state, select the corresponding Index Columns and hit OK. Your result should look like this. We don’t see the data yet inside the SupportCallRecord table which we just merged with the CountryList table. Let’s click on the double arrowed icon.

You will be presented the following default setting in a pop-up window containing the Column Names from the merged SupportCallRecord table.

Uncheck Index as well as Use original column name as prefix and hit OK.

Now we’re talking! Let’s do the final step in our merge process. Keep the Merge1 Query highligthed in green and on the Home Tab select the down arrow next to Merge Queries. This time, select Merge Queries (remember? last time we selected Merge Queries as New).

From the drop-down, select TranslationTable. Next, select the Index Column Name in both tables Merge1 and TranslationTable. Leave the Join Kind default settings and hit OK.

Again, you are going to have to “unpack” the data which is being held in the merged table, just as you did before.

Select the double arrow next to the TranslationTable column name, and a window will pop-up. Select English Translation and uncheck the other boxes. Then hit OK.

Bingo! We’re almost done. All your data is in one table now.

Since we do not need the Index Column anymore, right click on the Index Column header and select Remove. Before we move on, let’s change the name on the right hand side under Query Settings to a more meaningful one. Under Properties > Name enter SupportReport.

As you can see, we were able to mash up data in different formats into one table. Let’s now save our work. On the Home Tab, click on the down arrow next to Close & Load. Select Close & Load to. Selct Only Create Connection, then hit OK.

Now, let’s load the result into Excel. Under Queries & Connections, select SupportReport, right click it and go to Load To… In the pop-up window, select Table.

Before you hit enter, notice the highlighted field in your Excel Sheet. That’s going to be the first column’s header location unless you decide otherwise. In your Excel Sheet, select the cell A1.

Now let’s hit OK.

Your SupportReport is ready to to be presented to your colleagues and superiors. Hit Save and let’s move on to the second part of Lesson 5 to discuss what we just did and, more importantly, why we did that.

#5 Lesson — Part 1 | Learn to Code with Microsoft M-Language: Real World Project — Mashing up Data

This is my entire course

Written by SPRDsheet