What is Power Query in Power BI: A Beginner’s Guide

Power BI is one of the best data visualization tools on the market.

But what is it that makes Power BI such a popular tool?

There’s the cost: it’s cheaper than most other tools like Tableau.

There’s also the connectivity: it’s a Microsoft tool, and so the go-to data visualization software for most Microsoft users.

But there’s also another feature that makes Power BI actually easy to use for everyone, from data visualization consultants to everyday users to beginners:

Power Query.

In this blog, I’m going to tell you what it is, explain what it does, and walk you through 5 of its best features.

I’ve also answered some important FAQs at the end to help you clarify some concepts, like the Power Query editor in Power BI.

Let us dive in!

• What is Power Query in Power BI?
• Top Features of Power Query in Power BI: Breakdown for Beginners
• Transform Your Data in Power Query with Augmented Tech Labs
• Power Query in Power BI: FAQs

Power Query is Power BI’s native Extract, Transform, and Load engine built on the M language programming framework, whose job is to help you prepare data for visualization in Power BI.

But what exactly does all this mean?

Think of it like this: if your data visualization (like a Power BI dashboard) is the finished meal, Power Query is the kitchen where you prepare the ingredients for it!

Simply put, it helps you extract data from sources like: 

• Cloud warehouses.
• Excel sheets.
• Websites.
• Databases.

 Once you’ve extracted the data, you can transform it: 

• Standardise fields.
• Fix dates.
• Remove errors. 
• Combine tables into one. 

The last job of Power Query is then to help you load the data into Power BI. This happens via its native and deep integration with Power BI itself!

Also Read: Power Query Excel vs. Power BI - Which is Better?

Power Query in Power BI Examples

To understand how to use Power Query in Power BI, let us take a small example:

Let’s say you’re a sales manager and have two sales reports:

• An Excel file with a date column that has mixed formats (dd/mm/yy, mm/dd/yy, mm/dd/yyyy, etc.).
• A CSV file from your website with product names in ALL CAPS. 

In this case, you can use Power Query in Power BI to:

• Load both these files into the Query Editor with 2 clicks.
• Standardise the date column by using the “Change Type” > “Date” function.
• Change the product fonts by clicking on “Capitalise Each Word”.
• Remove any blank rows that might be there in the middle with one click.
• Use “Append Queries” to combine the tables together in one click.

And, finally, you can load the table straight into Power BI Report Builder to create your first report!

In this section, I want to give you a quick overview of the 5 most common features that you might have to use at the beginning of your Power Query journey:

• Connect & Combine
• Clean & Shape
• Pivot & Unpivot
• Automatic Data Type Detection
• Applied Steps
  
  

1. Connect & Combine

This will probably be the feature you use the most.

Its task is simple: to help you bring data from several different sources into one table.

Let’s take one of the simplest Power Query in Power BI examples to understand this:

Say you have two (or more!) tables with the same type of information: Sales-January, Sales-February, and so on.

You can use Connect & Combine to stack them into one long table! This is what we call the Append function.

But what if the two columns you want to combine aren’t of the same type?

In that case, you can use the Merge function. For instance, you can take two different tables, like:

• Table A: Customer IDs + Order ID
• Table B: Customer IDs + Customer Name

And merge them based on the common field: Customer IDs!

However, the two tables will definitely have to be related to each other in some way, i.e., by at least one common field!

2. Clean & Shape

This is the function that helps you fix common errors and organise messy information in your datasets.

So, how exactly can you use it?

Most of the Clean & Shape Queries are one-click functions that you can use to:

• Remove errors, duplicates, or blank fields.
• Filter rows to hide them when you don’t need them.
• Split columns like Full_Name into First Name and Last Name.
• Replace a set of values with another (0 to N/A). 

This way, the Power Query editor in Power BI helps you save time and clean your data so that your data visualization process remains as simple and smooth as possible!

3. Pivot & Unpivot

Next up, we have a very interesting Power Query function: Pivot and Unpivot.

And its name holds the key to understanding it:

It helps you convert rows into columns and columns into rows!

Let’s quickly see how Power Query does this:

• Pivot: Power Query will take a set of unique values from one column and create new column headers with them. 

For example, if you have a list of sales in one column and the months in the next column, it will make different columns for each month.

This way, you will have a month-wise sales table!

• Unpivot: This does the opposite. Power Query will take a row of Column headers and turn them into a single column of unique values.

Let’s take the same example here: say you have a wide list of sales by month. Clicking Unpivot will consolidate it into a tall list!

This function is very important because it helps you organise data such that it is ideal for:

• Storage.
• Analysis.
• Visualization.
  
  

4. Automatic Data Type Detection

This is one of my favorite functions: it automatically detects the kind of data in a given column.

So, when you first scan a file using Power Query, it will automatically assign a small icon next to each column:

• "123” for Number/Integer
• "ABC" for Text
• "Calendar" for Date
• "$" for Currency

Now, Power Query is not perfect. So, the detection might not be accurate every single time.

But the good part is that you can manually change it in case the auto-detection isn’t accurate.

One reason this is crucial is that without the correct description for each column, you won’t be able to apply the relevant filters.

For example, Power Query will let you filter only the “date” column by month or the sum function for the “currency” column.

So, if you want Power Query to automatically show you the relevant filters that should be applied to each column, you must either use Auto Detect or label the columns manually!

5. Applied Steps

Lastly, we’ve got Applied Steps.

Here’s how it works: it automatically saves and lists every single step you’ve taken in the course of cleaning your data.

Let me show you one example of what it does:

• Open the Power Query editor in Power BI.
• Go to “Applied Steps” - you’ll find it on the right side of the screen.
• There, you will see a list of steps, like “Pivot”, “Unpivot”, etc.
• You can click on any step, and Power Query will show you what your data looked like at that particular point.

And here’s the best part: you can delete one of the steps to undo that specific action without affecting the later steps!

That was a lot of information!

And if you’re a beginner, things can get very confusing…

So, let me quickly recap the key points we’ve covered:

Power Query in Power BI is the native extract, transform, and load engine.

It helps you gather data from different sources, clean it with a lot of different “queries”, and load it straight into Power BI for data visualization.

Now, it’s time for you to take your first step into the world of Power BI by using Power Query to clean a dataset for visualization!

And if you get stuck?

Not a problem: reach out to us at Augmented Tech Labs - one of India’s leading data visualization consultants!

From learning how to use Power Query to creating a complete data visualization infrastructure, we can help you leverage the full capabilities of Power BI, one step at a time…

1. What is the Power Query Editor?

The Power Query Editor is the dedicated window inside Power BI Desktop where all data cleaning and shaping happens. It’s where you access the tools we’ve explored in the blog and see your “Applied Steps”!

2. Is Power Query available online in Power BI Service?

Absolutely: you can use the Power Query Editor for transforming data on the Power BI Service. This wasn’t the case earlier, when it was only available on Power BI Desktop. But it is not available on the Power BI Service as well.

3. Does learning Power Query require coding?

No, you won’t need to write a single line of code to use Power Query. Most transformations use a graphical, “point-and-click” interface. However, advanced users like data visualization consultants and analysts do have the option to write custom formulas in M language.