Start Learning Contact Us
Power BI Mastery

Getting and Transforming Data with Power Query

Lesson 2 of 10

Every Power BI report starts by connecting to data and preparing it. Power BI Desktop uses Power Query for this, the same tool you met at the end of the Excel course.

Connect to data

On the Home tab choose Get data. Power BI connects to files such as Excel and CSV, to databases such as SQL Server and PostgreSQL, to online services and to many other sources.

After you pick a source, a Navigator window lists what it contains. Select the tables you need and choose Transform Data to open the Power Query Editor. Choosing Load skips the editor, which is rarely what you want on a first import.

The Power Query Editor

Part Purpose
Queries pane (left) One query per table
Data preview (centre) A sample of the rows after the selected step
Query Settings (right) The query’s name and its list of Applied Steps
Ribbon The transformations

Each transformation becomes a step. Steps run in order, every time the data refreshes. The source is never modified.

A checklist for every table

  1. Promote headers if the first row holds the column names.
  2. Remove columns you will not use. Fewer columns make a smaller, faster model.
  3. Set the data type of every column: whole number, decimal number, date, text.
  4. Trim and clean text columns.
  5. Remove duplicates from columns that should be unique, such as a product ID.
  6. Handle errors and nulls with Replace Errors, Replace Values or by filtering rows.
  7. Rename the query and its columns to names a report reader would understand.

Reshaping

  • Unpivot Columns turns a wide table with one column per month into a long table with one row per month.
  • Split Column separates combined values.
  • Group By summarises rows, although you should usually leave summarising to measures.
  • Merge Queries joins two tables on matching columns.
  • Append Queries stacks tables that share the same columns.

Adding columns

Add Column > Custom Column creates a column from a formula. Column From Examples works out the formula from values you type. Conditional Column builds if-then-else logic through a form.

Check data quality

On the View tab, turn on Column quality, Column distribution and Column profile. They show the share of valid, error and empty values in each column and how many distinct values it has. By default profiling is based on the first 1,000 rows; you can switch it to the entire dataset in the status bar.

Import or DirectQuery

When you connect to a database, Power BI asks how to store the data.

Mode How it works Good for
Import Data is copied into the model and refreshed on a schedule Most reports; fastest to query
DirectQuery Each visual sends a query to the source Very large data, or data that must be current

Start with Import unless you have a clear reason not to.

Close & Apply

Choose Home > Close & Apply. Power BI runs the steps and loads the tables into the model, where the next lesson picks up.

Practice

Import a CSV of sales, set every data type, and use Column quality to confirm there are no errors or empty values in the key columns.