Hi ,

Getting Started With Power Query

Efficiencies gained from Power Query can be life changing. I’m not exaggerating.

Power Query can reduce time spent gathering and cleaning data by up to 99%!

Imagine if all you had to do was click a single button and all the boring and laborious data gathering and cleaning was done for you. What would you do with all that spare time?

However, a lot of people find Power Query daunting, but it needn’t be. In this tutorial I’ll get you up and running with it in 3 easy steps.

Tip: Power Query is available in both Excel and Power BI, making these skills transferrable. I’m going to demonstrate it from the Excel interface, but it works the same in Power BI. 

Watch the Video

What is Power Query

Power Query is an ETL tool that:

1. Extracts
2. Transforms
3. Loads data.

But better than that, it enables you to automate the gathering and cleaning of data.

Once you create a query it can be used again and again to gather and clean data with the click of a single button.

Power Query can get data from many different sources including:

• Excel, Text, CSV files
• Web pages
• Databases
• Pictures
• PDFs
• SharePoint/OneDrive
• And much more

Step 1 – Get the Data

Use the Get Data menu on the data tab of the ribbon to specify where the data is you want to get, or use the shortcut icons:

In this example I’ll connect to a CSV file that’s in a file on my PC. Once connected to the data source, the Power Query editor window opens and the first 999 rows* of the data are displayed:

* Power Query only displays the first 999 rows to enable it to be super responsive while you’re making transformations and working in the editor. When you load the query, the full data set is loaded.

Step 2 – Clean the Data

The editor window is full of point and click icons for common data cleaning tasks. The Transform tab shown below contains just some of them:

Another common data preparation task is adding columns with calculations and lookup formulas.

The Add Column tools enable you to write IF formulas with drop-down lists:

And the Column From Examples tool uses AI to write formulas for you based on examples you give it. 

For example, I want to add a column that contains the month name extracted from the DateTime field. 

I simply type one or two examples, Power Query detects that I want the Month Name and writes a formula for me:

Check out the video above for more examples.

 

Step 3 – Load the Data

Once you have finished making the transformations, you’re ready to load the data to Excel or Power BI where you can begin analysing the data.

The first time you load a query you can choose the Close & Load To… option which allows you to specify where you want to load it (you can change it later):

Note: in Power BI ‘Close & Apply’ loads the data to the Power Pivot data model:

What Power Query Isn’t Used For

A common misconception is that we use Power Query to analyse and format data. We don't. 

The sole purpose of Power Query is to gather and clean data and load it to Excel or Power BI.

The analysis and formatting of the data into a report is done with formulas, PivotTables, charts/visuals etc. once you’ve closed & loaded the query.

Get Started with Power Query

Now that you see how easy Power Query is to use, I’m sure you’ll want to keep exploring. Check out the links below:

🚀Get up to speed fast with our self-paced Power Query Course

🎓 Introduction to Power Query video

📊Excel Dashboards with Power Query & Power Pivot Video

🔖50+ Power Query blog posts

Please Share

If you liked this please share this tutorial with your friends and colleagues.

Have a great day,

Mynda Treacy

Co-founder My Online Training Hub