Power Query Basics Guide Ask Us

General Overview

Welcome to this Power Query Basics help guide*. This guide will provide information on:

• how to ingest data in Power Query;
• how to filter and combine your files;
• how to clean and transform your data;
• how to merge and link your data tables
• how to gain insights from your data;
• how to automate and update your data.

In this research guide, we’ll use a series of fictional environmental behaviour surveys collected over a dozen Canadian cities to learn how to clean, combine, and analyze data efficiently using Power Query in Excel. Just a quick disclaimer: the data tables we will be using in this series are fictional. They were created using an R script that randomly generates numbers and assumptions and in their aggregate, don’t represent anything based in reality. They are simply used for explanatory purposes.

If you would prefer to consume much of the material offered in this guide via video, you can click here to view my Introduction to Power Query video series.

Basic components of a Power Query report

Before we get started, what is Power Query? Power Query is mainly used to import data from multiple sources into Excel or Power BI and then clean and transform them systematically. It can be a real time saver especially if you’re dealing with larger datasets. In fact, Power Query is not limited to the number of rows and columns that constrains Excel so it is an ideal candidate when dealing with large datasets. 

Let’s take a look at the Power Query environment so that we all have a common language and understanding of what is what. This is a view of the Power Query editor. In the centre of the screen (in yellow highlight), we have the data that we have ingested.

At the top, we have the tabs (File, Home, Transform, Add Column and View) with their associated ribbons. On the left-hand side we have our Queries. Right now we only have 1 query called Table1 because that is the source name of our data table.

Just above the data, we have a formula bar with some weird looking code. This is the M language which stands for Data Mashup Language. We won’t get too involved in M language programming in this introductory session but this is what makes Power Query verifiable and replicable.

On the right hand side, we have the Query setting pane which has an editable Query name field and the transformation steps that we did to our data. Right now we only have two steps. If we click on them, the corresponding M language code for that step will appear in the formula bar.  

*Compatibility

It is important to know that Power Query is not a static program and does come in many different flavours: Power Query for PCs has various versions and while most of the functionality is retained from one version to the next, each iteration creates some changes to the platform. Please be aware of the program and version of Power Query you are using. For your reference, we will be using Power Query for Microsoft 365 (PC version) as our help guide version. 

Ingesting Your Data

Scenario Background

Imagine your research supervisor gives you this CSV file of survey responses which you have opened in Excel. This survey was taken in 2022 and it polls students (from high school, college and university) in 13 Canadian cities in 5 different provinces about their thoughts on Energy Retrofitting.

Some of the cities have already engaged in a energy retrofit promotional campaign and others have not. Our supervisor is interested in finding out if living in cities with active promotional campaigns had any effect on their attitudes towards it.

The survey respondents are questioned on their attitudes before a brief discussion about retrofitting and after it. We see that each row is one respondent and that there are 10,000 respondents in this file. The fields or columns are Collection Date, Education Level, Gender, Age, Annual income and a before and after energy Retrofit Attitude score which ranges from 1-5 where 1 is low, 3 neutral and 5 is high.

Note the clean data structure of this dataset:

• Headers are at the top row followed by rows/records
• No empty rows or columns
• Each column/field has only one data type
• No totals, subtotals or other non-record information in the rows.

When working with a datasets, or constructing your own datasets, it is important to keep these points in mind especially when you want to incorporate your data in Power Query or other database-type software. Excel allows you to be flexible when structuring your data which can be a feature but when you want to process data systematically, you need to be mindful of proper data structure.

Your research supervisor also hands you a data dictionary. A data dictionary gives insight and context to the data like explaining what each column or field means. It can sometimes also contain more information on the data like the context for which it was collected, how it was collected, who did the data collection and/or analysis as well as other features like what the breakdown of responses was and what the response codes mean. It is best practice to always start by inspecting the data dictionary first to get a good handle on the data you will be working with.

Task: Your research supervisor asks you to clean this up and to have a tidy version of this data table on their desk in by the end of the day. Specifically, they want to see retrofit attitude data for female university students between 20 and 25 years of age. Now you could do a decent job of this by using Excel functions but we are dealing with a rather large data set and some of the functions could get a little complicated. Plus, your supervisor wants you to document any change so that they can understand how you’ve come up with the final product. So given that, you think that using Power Query could be ideal to make some quick transformations.

Scenario Activity

Ok, but how do we get this data in Power Query? To do so, we’ll go to the Power Query section of Excel which is in the Data ribbon.

Next to the Get Data button, we have some common options of how to get our data but in this case, since all the data we want is right here in this spreadsheet, we can choose the Table/Range option. Although we have not converted our data into an official Excel table, as soon as we use this Power Query option, Excel converts our range into a table which it creatively calls Table1. This will bring the data in the Power Query editor, which we saw in the previous tab's illustration.

Now that our data is in the Power Query editor, let’s do a few transformations. This is a big dataset and maybe we are only interested in a sliver of the data to answer one of our research questions. Let’s say that we are only interested in answering our supervisor’s question: analysing respondents who were Female undergraduate and graduate students between 20 and 25 years of age who lived in NB.

Let’s select New Brunswick in the Respondent ID column. We do this by clicking on the dropdown arrow of the column and selecting Text Filters and selecting "Contains" from the dropdown menu. We then type in New Brunswick.

Now only entries with New Brunswick will be selected bringing our table records down from 10,000 rows to 277 rows.

Lets select undergraduate and graduate from educational level.

This brings the dataset down to 148 rows.

We can do the same method to select Female as the gender which brings the dataset down to 67. And we bring the dataset down to 48 by using the same technique on the age field and selecting ages between 20 and 25 inclusively.

This dataset now looks much more manageable and targeted. Let’s try to clean it up a little more.

• We don’t need the Date Collected or Annual Income columns for our analysis so we can just delete them by clicking on the column header and pressing the delete button. 
• We also would like to see the Gender and Age columns followed by the Educational Level so let’s move that. We can do that by clicking on the column header and using our mouse to bring it to the desired location in the dataset. 
• Finally, we can change the text of the column headers by double clicking on the title and renaming them. The text for the Attitude Survey is a bit wordy so let’s truncate it a little bit by removing the "EnergyRetrofit" text. 

We can see that there are many transformations that have occurred and that all the steps are documented on the Applied Steps pane at the right of the screen. This is perfect because our Research Supervisor can inspect our steps and even recreate our method very easily.

We can change the name of the steps and add notes to give us more insights on what it is doing by right clicking on it and selecting Properties. We can move steps up or down but we need to be careful when we do that because sometimes things can break because they are not properly linked anymore. We can also always delete a step.

When we see a gear icon next to a step, we can click on that to see the mechanism behind the step and make changes if necessary. Also, the M code for each step is being displayed in the formula bar and if we know what we are doing, we can make changes to the code as well. 

Let’s change the name of our query to TableNB2022 so that we know what this is doing.

Note that the query name also changes in the query pane. This looks pretty good for now.

Once we are done and satisfied with our changes, we can press the Close and Load button and Power Query will load our transformed data as a data table in a new sheet of Excel.  

When that happens, we see a Queries and Connections pane on the right-hand side of the screen.

If we close that pane and want to get it back, we can always access it by clicking on the Queries and Connections button in the Data ribbon. If we wanted to access this query again, we simply need to double-click on it and you’re right back in the Power Query editor.

Post-Scenario Analysis

A word of warning here. We are doing all this in a CSV file. CSVs are great because they are flattened data files that can get ingested by a lot of different data tools. Unfortunately, they are not super functional so they can only have one sheet for example. They also don’t support Power Query. So if we were to save this, we would lose our Power Query work and only one sheet would be saved. The way to get around that would be to save this document as an Excel file or .xlsx file and that would retain all the integrity of this document. Another way to do this is to access the CSV data remotely from another Excel file and that’s something we will learn how to do in the next section.

Alright, just a quick aside, one of the great side benefits of using Power Query is that the original data is never altered. It is good practice to keep the original data intact. In this case, we are not touching the original data which is unaltered in the other sheet. We are just using the data from that sheet in Power Query, transforming that data and then generating our own analysis based off of it.

But, if we were to change something in the source file, like a header name or the Table name, it would compromise the Power Query code. This is because the M language stores references as literal strings so changing these references breaks the code. So remember that if you change something, a reference, the position of a step, etc. it may impact the code downstream and you will need to fix this in order for Power Query to work properly again.

Filtering and Combining Your Files

We will now continue our discussion of ingesting data in Power Query – but this time by combining files. 

Scenario Background

Your supervisor was satisfied with the data table you created in the previous section. But they want you to keep working with the data to provide more workable insights. And, they also tell you that there are 6 other CSV files representing 6 other years of the survey that they want incorporated in the data table. They are found in a folder and they all have the same data structure as the first survey we already worked on.

Scenario Activity

The first step is to go back to the Data tab in Excel and try to ingest all those files in Power Query.  If we click on the Get Data button, we have  options to ingest data from several different sources but the one we are interested in is the “From File” and then select “From Folder” since all our data is in the same folder.

We’ll navigate the file explorer until we get to the folder with our data and press the Open button. From there, we can see, among other things, all the survey files in that folder which are all called Field_Survey_Year.csv.

If we were happy with all of this, we could press Load button at the bottom right of the pane but we want to modify these files so we would select the Transform Data button. In fact, it’s usually good practice to transform the data so that you can at least look at it before your load it. We could use the Combine button if we wanted to combine all the files together but remember that we have 7 field surveys that we want to combine but we don’t want them to be combined with all the other files in the folder.  

In order to combine only the field surveys we will need to filter out the files we don’t want to use right now. So let’s press the Transform Data button to get to the Power Query editor.

Here we see that there are dropdown filters on the field or column headers. Let’s click on extension and select CSV files since that is all we are interested in for this example. Now we only see the CSV files in the folder. 

Let’s click on the Name column dropdown filter and let’s select Text Filters/Begins with (We could use Contains but all our files begin with it so that is more appropriate for our use case). We can type in Field_Survey_ and now only the files with field survey are shown. 

Now that we have filtered out the files we don’t want, let’s combine the files that are left. To do so, we can click on this double arrow icon in the Content column to combine all the remaining files. As the default setting, Power Query will use the first file as a sample and append the rest automatically. We can see that there are many transformations that have occurred and that all the steps are documented here just like in the previous example. And just like before, the M language code for all the steps is here in the formula bar.

So we can see that not only have all our files been combined and the fields had the top row promoted to header status, the data type changed. Power Query guesses as to the data type depending on what it sees in the field. And it’s usually pretty good as you can see. You can always change them if they are not getting picked up properly by clicking on the data type icon to the left of the field title and selecting the desired data type. 

Before we do a few modifications to the data, I always like to do a quick check to see if it matches my expectations. One of these checks is the count. I expect that each year will have 10,000 respondents so each file should have 10,000 rows. Let’s do a quick groupby, which is basically like a pivot table of the counts per file name.

• Let’s click on the Group By button in the Transform tab.
• Select Source.Name as the desired field
• Name the new column can be anything, let's stick with the default: Count
• Operation to Count Rows
• Click OK

We can see that all the years are 10,000 expect for 2026 which is showing 10,008 which we would not expect.

What could be causing this? Maybe they inadvertently surveyed more people than they should have? Maybe it’s a data error? Let’s test that.

Let’s delete this Group By step – it was just  a spot check, not a data transformation we want to keep.

Let’s click on Respondent_ID header. Let’s right click on it and select Remove Duplicates.

If we do another GroupBy of the Source_Name, it will show 70,000 and not 70,008. That means that the 8 extra records were duplicates.

So we used the source name to do our Groupby. Sometimes that’s a very useful field but at this point it’s pretty redundant so let’s eliminate this column. We can do this by clicking on the header and pressing the delete button. Alternatively, we can also press on the remove column button in the Home page ribbon. We can see that this extra step has been applied to our list of steps in the Query Settings pane. We can also see that the corresponding M code for this action is visible in the formula bar.

Let’s do a few modifications to this dataset before we close this out. The first column is full of great information but it’s all smushed together in one cell. We can split the contents of this field into four different fields: Year, City, Province and ID. We can use the Split Column button in the Home tab ribbon to do this. It’s also available in the Transform tab ribbon.

• In the Transform tab, use the Split Column button
  • by delimiter
  • use the custom delimiter
  • enter space, hyphen, space: " - "
  • and we want to split it for each occurrence.

Let’s add a prefix to the ID field to make it more explicit that it is a tag instead of a number. We can do this by clicking on the ID field and then going to the Format button in the Transform tab ribbon and selecting Add Prefix. Let's choose "R-" for respondent.

We can now rename the split columns (Year / City / Province / RespondentID) by double clicking on the title and renaming them. We can also drag the ID column to the beginning of the dataset. 

Ok, now that we are almost done, let’s change the name of our query to FieldSurveysAll so that we know what this is doing. Note that the query name also changes in the query pane. This looks pretty good for now.

We have a few options now if we wanted to continue to work with this dataset. We could close and load which would export this entire dataset in Excel. At this stage, this doesn’t seem wise since it is such a large dataset that it would not only make it cumbersome to work with but might slow Excel down since it is dealing with such a large amount of data. For now, let’s click on the Close and Load dropdown menu and select Close and Load to. We have the option of a table (which is just a data export in Excel), a pivot table report, a pivot chart or to only create a connection. Let’s select the latter here.

This will create a connection with the original data with all our transformation but will not bring all the data in this file which saves memory. It is also a more flexible option since we can reference this live connection again with another query rather than being locked in to fixed output. There is also an additional option at the bottom: Add this to the Data Model. We haven’t discussed the data model yet (although we took a quick look at it in our Power BI video series) but let’s click on this option as well which will be helpful in future videos.

Once we are done, we see a Queries and Connections pane on the right-hand side of the screen. We don’t get an output this time like in the last video because we just created a connection for now so the data is in the background ready to be used later. If we wanted to access this query again, we simply need to double-click on it and you’re right back in the Power Query editor.

Post-Scenario Analysis

Again, just a quick aside. Whether we decide to load our Power Query data in Excel or just create a connection with the data, the original data is never altered. It’s good practice to keep the original data intact. In this case, we are not touching the original data (which are the CSV files in our folder). This is not only ideal from a file size/efficiency perspective (which means that our file may run faster than if we had all that data in it) but it’s also in-line with the principle of leaving the original data intact. And unlike the example in our first video, this time we are working in an Excel file so we can save this file and when we reopen it, all our Power Query queries along with their ouputs will be saved.

Cleaning and Transforming Your Data

We will now focus on cleaning and transforming our combined dataset so that it can be ready for analysis.

Scenario Background 1

Your research supervisor is happy on how you combined and disaggregated some of the data in the previous video. They would like you to take another look at the data file to see if there are other helpful transformations you could do to clean up the data and to create some aggregations before moving on to the next step.

Scenario Activity 1

The first thing we should do is get acquainted with our data. To do this, let’s click on the View tab in the Power Query editor and select the Column profile box to get the column statistics along with a distribution visualization for our data. And let’s click here and select column profiling based on entire dataset. Depending on the size of the dataset, it could take a little while to do the calculations. 

When we click on the Province field, we notice that sometimes the data is entered with the full province name and sometimes it is entered using the two letter abbreviation. After a little digging, we’d find out that this depends on the year the survey was taken so perhaps the surveyors got different input information over the years when it came to the Province field. In any event, we’ll need to clean that up.

We can solve this one of two ways. We can click the header of the Province field and select the Replace Values button (you could do this for all the provinces and maybe change the name of the steps to know what step does what transformation).  

 

Another way to do this is to create a lookup table query. We do this by pressing on the “Enter Data” button in the Home tab ribbon.

We get a popup panel where we can create a new table. Our table will consist of two columns: the lookup column and the replacement column. We’ll just fill out the six provinces that we want to change in the lookup column and place the text we want to replace it with in the replacement column. Let’s also add the full names of the provinces in the lookup field. This way, whether the name comes in as an abbreviation or a full name, it will be replaced with a full name. We can add column headers (Lookup and Province) and a name for the table itself: ProvinceLookup. Press ok and we can see that it become a query.

Ok, now that this is done, we can merge both tables: our FieldSurveyAll table and our ProvinceLookup table by pressing on the “Merge Queries” button in the Home tab ribbon. Let’s highlight the FieldSurveyAll query and press the button. We can then select the field that the lookup table will lookup, so in this case, the province. Then we can select the table we want to work with (ProvinceLookup) and select the lookup field in that. We can pick the kind of join we want, in this case, we will leave it as the left join which is the most popular and it’s akin to doing an XLOOKUP() if you are familiar with that function in Excel. When we look at the bottom of the popup window, we have 70,000 out of 70,0000 entries that match – perfect!

When we press ok, we have the ProvinceLookup table merged onto our FieldSurveyAll table. That’s a good start but not quite what we want. Let’s press on the double arrow icon and pick what we want to retain. We just want the replacement value (not the lookup value) and we don’t want to include the original column prefix.

We now have a province column that only has the full provincial names that can replace our original province column. We can now delete the original Province field and replace the header name of our new field with the Province title. That’s a nice part of Power Query versus Excel. You can delete your source material if you don’t need it anymore (in this case the Province field) and the output from it remains (in this case the modified Province field).

These are two ways of replacing data in your dataset. The replace feature is great when you have a few replacements and the lookup table saves you time if you have a significant amount of replacements but they both do the same job.

Let’s move on to annual income. Go to view/column distribution and show 0 empty in statistics even though there are clearly empty cells in field. We also see some blanks – this means that there is something in that cell or else it would be written null.

It’s likely a trailing space. Let’s fix that.

Let's select the Replace Values button that can be found in the Transform and Home tabs. Let's put a space in the first window and the word null (no quotation marks) in the second. Now we have empty cells showing up. But note that there are no errors, even though there are errors in the column. That’s because we haven’t assigned a data type yet. Those error texts could be valid if they were text data type for example where we would mark something as an error. It’s not necessarily wrong in certain contexts. So let’s assign the data type of currency and see what happens. Now we have a red bar followed by a crisscross line which means that Power Query has identified errors in the field. It’s saying some of these values don’t make sense if your data type is currency.

Let's use the Replace Values button but this time the feature we want (the dropdown menu) is only available in the Transform tab. Let's select replace error.

Sometimes it makes sense to replace with 0, or an explanatory string. In this case, let’s just put null to make it an empty cell since we can’t make an assumption on what the value ought to have been.

Notice that the red bar followed by the crisscross line is now gone and the data is deemed to be in congruence with the data type.

Now that our data is cleaner, let’s create a new column for age groupings so that it’s easier to analyse ages. When we create groupings like this, we sacrifice some granularity in order to gain in what I call data digestibility. It’s up to you to figure out what the right balance is depending on your needs and your audience.

Let's select the Conditional Column button in the Add Column tab.
We’ll name this field Age_Group and will create our set of conditions.

So this created a column based on the Age field that has “Teenager”, “Early 20s”,  “Late 20s” and “30 and over” as outputs. 

We can create another conditional column using the same technique. This one will be based on the Education_Level field and we will call it Is_University. Here are the conditions:

• If Education_Level = “High School” → “No”
• If Education_Level = “College” → “No”
• Else → “Yes”

This will allow us to see if there are variations in the data between University students and other students. Now it’s important to realise that this conditional column can create blind spots. For example, if educational level was elementary, it would say yes to Is_University because elementary is neither of the No conditions (High school or College). Same with blanks in the lookup cell, they will go down to the Else unless defined earlier. Errors in the lookup cell will create an error output. So it’s important to know our data well when creating these conditions.

We can find the change in pre and post attitudes by highlighting our two fields, then pressing on the Standard button in the Add Column tab ribbon and pressing Subtract.

We want the Add Column tab because we want the results to be in a new column otherwise we could go to the Transform tab and there is also a Standard button there but pressing Subtract would Subtract a number in all of the cells of the column.  Ok, let’s change the header to Attitude_Change

Scenario Background 2

Just when we think that we were done, our Research supervisor tells us that they just got their hands on an environmental consultant’s data that analyzed each city’s advertising funding levels for their respective environmental programs. The output is in a PDF format, not a CSV file. When we click on it, we see that this dataset records the City and Province, the year the program started, the funding levels in millions, a program description and the population of each city. Again, note the data structure of this file is similar to the one in the field surveys: headers at the top, no empty columns or rows, each column only has one data type, no totals or subtotals, just a series of records. Our research supervisor would like us to take a look at this dataset

Scenario Activity 2

Let’s start by ingesting this data in Power Query by going to that section of Excel in the Data ribbon. We can press on the Get Data button and select File. From there we can select PDF.

We can select Page 1 which would be the entire page but Power Query has recognized the table within the PDF so let’s select that (Table 001) since that’s what we are interested in and press the Transform Data button.

Let’s take a look at our data inside the Power Query editor.  We can see from the steps that Power Query has correctly identified the table contents and elevated the first row as headers and detected data types for each field. The first column basically merges the city and province data. We already have the province information in our FieldSurvey dataset so in this case that information is redundant (especially since there are no two cities with the same name but with different provinces such as Cornwall Ontario and Cornwall PEI for example). In this case, we could just keep the city information.

To do so, let’s use the Extract function. We could put it in a different column if we wanted to by choosing that function in the “Add Column” tab but in this case, we’ll just want to substitute it so we’ll extract it directly in that column by choosing the function in the “Transform” tab. We can choose Text Before Delimiter and select the underscore as our delimiter. Now let’s change our header to City.

Next, let’s tackle the funding field. We can see that it is in millions of dollars but let’s make that more explicit using the actual number. We can do this by going to the “Standard” button in the Transform tab ribbon and selecting Multiply.

The value we want to put in is 1 million so that every cell in that field will be multiplied by 1 million. Now let’s change the type to currency and we can rename it to Program_Funding.

Finally, let’s add a column to calculate the per capita of the funding.  Each city has a different population so it would be nice to know how much each city spends on average given its population level. To do this, we go to the Custom Column in the Add Column tab ribbon. We can call this column Funding_Per_Capita and we calculate this by taking the Program Funding and dividing it by the Population.

But each program did not start at the same time. In fact, the Federal government has allocated this fixed municipal funding which needs to be spend by the end of 2026 but there is no prescribed start date. Some cities were able to establish the campaign earlier than others. So if we want to know what the annual funding per capita should be, we need to take the start year into consideration. So assuming we are in 2027 and the program is completed. We can add this to our formula (2026 - [Start_Year] + 1) in order to get the annual spend per capita.

We can change the data type but that adds another step so we can simply integrate this in the M code by putting in ,Currency.Type.  Now we can see that some cities spend single digits on this program on a per capita basis while other cities spend significantly more.

Now that we have transformed our dataset the way we want it, let’s click on the Close and Load dropdown menu and select Close and Load to. Again, let’s choose to only create a connection for now add let’s add this to the Data Model as well.

We’ll discuss the data model in the next step.

 

Merging and Linking Your Data

In this section, we are going to build from the previous steps and focus on merging our data and linking our data tables in the Data Model.

Scenario Background

Your research supervisor is impressed with how quickly and thoroughly you are handing the data. But just when we think that we have everything covered, they tell you that there is another team that they are supervising that has been working on another portion of the survey. It is dealing with the same survey respondents as we have been analysing over the past seven years but this portion of the survey captures the environmental attitudes of the respondents. Your research supervisor wants you to connect this data to the rest of the survey data somehow.

This new dataset is in an Excel file not a CSV file. When we click on it, we see that it records opinions from the same people in our survey but this time we have all 7 years’ worth of data in 1 file as opposed to seven separate files.

Again, note the data structure of this file is similar to the one in the field surveys: headers at the top, no empty columns or rows, each column only has one data type, no totals or subtotals, just a series of records. We have the respondents field, and the results of how they scored on three different metrics ranging from a theoretical 1 all the way to 100. Your research supervisor thinks that the three different metrics are too noisy and they want to simply see a differentiation between people who have High and Low environmental engagement.

Scenario Activity 1

Let’s start working on our new data. We need to use the three index outputs to create a differentiation between people who have High and Low environmental engagement just like our research supervisor asked for. Our Research Supervisor didn’t really set any parameters for this so let’s just create them for now. If they are not what our supervisor is looking for, we can always tweak them afterwards quite easily. So let’s assume that to be considered as having high environmental engagement, a survey respondent needs to achieve a 75 or more in all three categories.

We can try to create a conditional column to do this but we quickly realise that we can’t do it that way because the conditional column does not allow us to add an “AND” condition. So we have to use the M language to do this. Again, we're doing this to show you how to use the M language, and to gain a little familiarity with its structure, not necessarily for you to remember this exact formula.

So first, let’s click on the Custom Column button in the Add Column tab ribbon. What do we want to call this column? Let’s call it OverallEngagement

Now we need to type in our code. It’s not super intuitive but once you see it, you can quickly understand how it works:

each if [EnvConcernIndex] >= 75   and [PolicySupportIndex] >= 75  and [PersonalResponsibilityIndex] >= 75    then "High"  else "Low"

We can now press the OK button. 

A couple of things to keep in mind. Note that Power Query completes the M language code from our query.

It added the formula name to add a column: Table.AddColumn(). In fact, if we wanted to see more about this, we could go back in the Added Custom step, pressing on the gear icon and pressing on the link at the bottom.

It brings us to a website with all the Power Query functions grouped by category. If we pick the Table functions, we have whole host of functions (table construction, conversions, information, row operations, column operations, transformation). When we click on Table.AddColumn(), we see its syntax, a blurb about it and an example.

So we note that it starts with the table, then the new column name then a column generator or what’s going to create the column (in this case, the formula we inputted). There’s also an optional column type. The formula returns the data as a table.

The first argument (table) can be a bit puzzling. What table? In our case, it says #“Changed Type” in the first argument, or the table argument.

So what’s happening here is that Power Query is using the end of the previous step as its source for the table. In fact, many of the M language functions use the previous step as the beginning for their formula. It’s a little like a chain of events where the steps are tied together within the formulas themselves.

So we figured out the table issue, and the new column name is the next argument which we assigned as OverallEngagement. Then we have our formula and we have the option of putting in the data type if we want. We note that the data type has not yet been identified in this column. We could use the detect data type button in the Transform tab ribbon or click on the data type icon of the column and change it to text but that would create an extra step.

Let’s keep it clean and instead simply fold that in to our custom formula by typing in ", Text.Type". Note that the data type is now changed and no extra step has been created.

Suppose we wanted to make this a little more sophisticated, and impress our research supervisor, we could add a Medium component say, from 50 to 74. How could we do that?  Let’s click on the gear icon of the Added Custom step. Let’s copy the conditions for High and paste them underneath it. Let’s make sure there is an "else if" after the “High”. Now we can change this to 50 on all three counts, and have then “Medium” and still finish with else “Low”.

Now we have three categories instead of two. We could continue to do this to add as much granularity as we thought was necessary. Also note that because we changed the formula, we need to go back and rewrite ", Text.Type" at the end of the M code to change the data type.

Now that we have our data for OverallEngagement, we could delete the three index columns. That’s one of the nice things about Power Query – it’s not reliant on the source data once something has been executed, unlike Excel. In Excel, if you delete the source cells, the formulas will no longer work.

That’s looking pretty good now. Let’s rename our query EnvironmentalAttitudes.

Ok, now we can begin to merge the two pieces of the survey together. This is very similar to the example in the previous step where we merged the different province outputs together using a lookup table. In this case, we will merge the FieldSurveyAll table with the EnvironmentalAttitudes table. Remember, we do this by pressing on the “Merge Queries” button in the Home tab ribbon. Let’s highlight the FieldSurveyAll query and press the button. We get the choice between Merge Queries and Merge Queries as New. This time, let’s click on the latter since we want a brand new query in this case. So we will select the field that the lookup table will lookup, so in this case, the Respondent_ID, then we will select the table we want to work with (EnvironmentalAttitudes) and select the lookup field in that, also Respondent_ID. We can pick the kind of join we want, and again this time we will leave it as the left join which akin to doing an XLOOKUP() in Excel.  We see that 70,000 of 70,000 entries have a match which is great.

When we press ok, we have the Environmental_Attitudes table merged onto our FieldSurveyAll table. That’s a good start but not quite what we want. Let’s press on the double arrow icon and pick what we want to retain. We just want the Overall Engagement field and we don’t want to include the original column prefix.

We now have that data field merged with the FieldSurveyAll dataset. Let’s rename this query as FieldSurveyAll+Engagement.

We could keep working on this dataset to try to find new insights with the combined data. Just an FYI, this query was created to show you how to merge queries together but we won’t be using it going forward as we already have the information we need in the Data Model which we will work with in the next video.

Scenario Background 2

After showing this to our Research Supervisor, they tell us that they would like us to combine all of the data sources together, the Field survey data, the Environmental Attitudes survey data and the City Program data.

We could try to merge all of this together and that would work but another way to combine the data is to use the Data Model which is much more scalable. We discussed the data model a little bit during our introduction to Power BI series where we linked our data tables. Let’s dig into it a little more here. The data model (which is confusingly also called Power Pivot) is not necessarily part of Power Query but Power Query allows us to export our datasets into the Data Model in order to link them together. So let’s start by doing that, let’s send our data to the data model.

And now that query should be in the data model. Just before we are ready to start working on the Data Model, our Research Supervisor comes in and tells us they think that applying a 75 for HIGH engagement in the environmental attitudes table is a bit too high and they recommend going down to 70. Otherwise, everything is perfect.

Scenario Activity 2

This is a pretty easy fix. Let’s double click on the query. We can either press on the gear icon of the Added Custom step and replace the 75s with 70s or do it directly in the formula bar. If we do it in the applied steps, we’ll need to re-enter the ,Text.Type at the end of the formula again. By doing it directly in the formula bar, that part of the formula remains intact so let’s do it that way.

Ok, let’s close and load this. Since this was saved as a connection and in the data model originally, that’s how it’s going to be loaded in again but this time with the updated data.

Now that all our data is ready, we can access the Data Model. We can do this by pressing on the data model button in the Data tab ribbon or by going to the Power Pivot tab and pressing on the Data Model button in that ribbon.

When we go to Diagram View, we can see our three tables there – matching our three queries that we put in the Data Model. Right now they are independent tables but we can link them via a common field.  

Let’s take our FieldSurveyAll table and link it to the EnvironmentalAttitudes table via the RespondentID field. We can do this by picking one of the common fields and dragging it onto the other corresponding field. This line denotes a relationship and if we click on it, it highlights the fields that form that connection. The 1 means that there is only 1 instance of RespondentID in that table so every RespondentID in that table is unique. The asterisk means that there are many instances of the same RespondentIDs in that table.

This is known as a 1 to many relationship. But because we know our data, we know that this should be a 1:1 relationship since RespondentID is unique in both tables. Power Pivot doesn’t capture the 1:1 relationships so it assigns a many to one of the tables. In fact, since this is really a 1:1 relationship, it would be optimal to use the merged table instead. I simply did it this way to explain the point.

So let’s go back and remove both the FieldSurveyAll  and the EnvironmentalAttitudes tables from the data model and instead let’s put in the FieldSurveyAll + Engagement in the Data Model since it captures all that data in one table.

Now if we go back to the data model, we can link the FieldSurveyAll + Engagement table with the City Program table via the common field of City. We will remember that there is only 1 of every city in the City Program Table and there are multiple examples of the same city in the Field Survey All Table. For example, Halifax shows up once in the City Program but many times in Field Survey All since it will appear anytime there is a survey respondent from that city. So with that, let’s create a link between these two by picking the City Field in the FieldSurveyAll + Engagement table onto the City Program table to be precise about the direction of the relationship.

Now we can see that there is a 1 to many relationship between these two tables as well via the two City fields.

There we go. We’ve now completed our Data Model with two linked tables giving us a clean and efficient structure to analyse our data across both tables simultaneously. If we were to receive more data tables that fit into this framework, we could include them in the Data Model as well.

I am currently working on the Power Query basics guide. Thank you for your patience.

Look for the next video in the Power Query introductory series as well as the associated tab in this guide: "Analysing your data" premiering Thursday, November 19th, 2026 at 10am AT.