Power Query (3/4) Technical Overview

The easiest way to do a technical dive into Power Query is to run through a mini tutorial to see it in action. Step 1 – Open PowerBI Desktop and click on Get Data. Up pops connections to hundreds of data sources. For our tutorial we are going to use a built-in sample dataset.

Power BI Get Data Screen

On the home screen is a tile to use sample data, click that. Choose the “financials” tab from the excel sheet and then choose “Transform Data”.

This will get us into the Power Query Editor. The Power Query editor is a pop-up window that allows us to add queries or modify existing queries for data pulls. It has an excel like feel with the ribbon along the top. You’ll see there are lots of data manipulation options.

Power Query Editor

Let’s filter the Segment column down and select only “Small Business”. You’ll see the “Applied Steps” along the right hand side now has a new step called “Filter Rows” which saves our selection. This is how Power Query is considered a “no-code” solution, however it does generate “M” code.

Applying a filter in Power Query Editor, Adds new step into Applied Steps

Let’s inspect the “M” code for this query by clicking “Advanced Editor” at the top. Notice the script now includes our step to filter Segment down to Small Business. Now each time we go to get the latest data, our process is smart enough to automatically refresh to how we define it in the Applied Steps section.

M code generated by Power Query

Now just hit “Close and Apply” and we will load all the data that is included in our Applied Steps section. Simple as that!

Although it can get more complicated with parameters and incremental refreshes, this is a quick way to save yourself from having to rebuild the same analysis every time you get new data in your source systems. Next time you find yourself building the same analysis over because some of your information has changed, give this a shot.

A Daily Email From Me

Give me a couple minutes of your day and I promise to provide thought-provoking messages to help you level up your BI knowledge and run more successful data projects.

A Daily Email From Me

Subscribe to get daily tips/ideas for how to best run a data project.

    We won't send you spam. Unsubscribe at any time.