Level 1 · Import only what the report needs

The first rule of building a report: bring in only what it needs. Every extra column makes the model bigger and slower, and every cancelled order is one more row someone has to remember to filter out.

Getting the data in Power BI Desktop

For a SQL Server table: Home → Get data → SQL Server, type the server and database, choose Import, then in the Navigator tick the orders table and press Transform Data (not Load). That opens the Power Query Editor with the table as its first step, called Source. For an Excel workbook it is Get data → Excel workbook, then the sheet or table in the Navigator; for a CSV, Get data → Text/CSV. Transform Data is the habit to build: you look at the data before it reaches the model.

What you are working in

This page is a practice editor for Power Query’s way of working, not Power Query itself. The Source step holds the imported table; you add steps one per line, and the preview shows the table after each one. The step names and results match Power Query’s.

Keep columns by name

Choose columns: order_id, customer_id, order_date, status

In Power BI: Home → Choose Columns, tick the four columns. Power Query records a step called Removed Other Columns. The M it writes:

Table.SelectColumns(Source, {"order_id", "customer_id", "order_date", "status"})

Choosing by name is safer than removing by name: when the source gains a column next month, your report doesn’t change.

Keep rows by a test

Filter rows: status = completed

In Power BI: click the arrow on the status header and untick everything except completed, or use Text Filters → Equals. The step is Filtered Rows:

Table.SelectRows(#"Removed Other Columns", each [status] = "completed")

Filter early, at the source

When the source is a database, Power Query can often send these first steps back to SQL Server as a query (it is called query folding), so only the rows and columns you keep travel over the network. Steps that can’t fold, like many custom columns, should come after the ones that can.

Common mistakes

• Loading straight from the Navigator. You skip the look at the data and the clean-up.
• Text case. Power Query compares text case-sensitively: Completed is not completed.
• Filtering in the visual instead. A visual filter hides rows; the model still carries them.