Connecting and Shaping Data in Power BI Desktop
Objective. Students import data from a CSV file, an Excel workbook, and a SQL Server or PostgreSQL database into Power BI Desktop and apply Power Query transformations to produce a clean, typed table.
This lesson, its worked examples and its practice questions has not yet been verified by an industry-certified professional or by a certification or approval body. It is a study draft and may contain errors. Do not use it as instructions for work on a job site or on live equipment. Always follow your instructor, your supervising professional, and current official references and regulations.
VoTechWorks program approval and accreditation are still in progress. Credentials are issued only by the certifying bodies named in each program, after their own exams.
Connecting and Shaping Data in Power BI Desktop
Why Data Preparation Matters
Before any chart or dashboard is built, the underlying data must be accurate, consistently typed, and free of structural problems. Power BI Desktop provides two tightly connected tools for this work: the Get Data dialog, which establishes connections to sources, and the Power Query Editor, which transforms raw data into analysis-ready tables. Errors introduced here—a column typed as text instead of a number, a stray duplicate row, a mismatched date format—will silently corrupt every visual downstream. Taking time to clean and verify data in Power Query is therefore a foundational professional habit, not an optional step.
Connecting to Data Sources
Power BI Desktop can connect to hundreds of source types. For this lab you will use three:
- CSV (comma-separated values): Choose Home → Get Data → Text/CSV. Power BI previews the file and usually detects the delimiter automatically. Always verify the delimiter is correct before loading; a semicolon-delimited file opened as comma-delimited will collapse all columns into one.
- Excel workbook: Choose Get Data → Excel Workbook. The Navigator pane lets you select individual sheets or named tables. Prefer named tables over raw sheets because they carry consistent column headers.
- PostgreSQL database: Choose Get Data → PostgreSQL Database, then supply the server address and database name. The class PostgreSQL instance credentials were established in Course 4. Power BI will prompt for your username and password; those credentials are saved per data source in the credential manager, not in the .pbix file itself, which is important for file sharing and security.
Every connection produces a query in Power Query Editor. Think of each query as a recipe: it records every transformation step and replays them in order whenever the data is refreshed.
The Power Query Editor Interface
When you click Transform Data, Power Query Editor opens. Key areas:
- Queries pane (left): lists every connected source.
- Applied Steps pane (right): records each transformation in sequence. Steps can be deleted, reordered with care, or renamed for clarity.
- Formula bar: shows the M formula for the selected step. Even if you never write M by hand, reading this bar helps you understand what Power Query is doing.
- Data preview (center): shows a sample of the data after the selected step is applied.
Essential Transformations
Promote headers. When a CSV loads with generic column names (Column1, Column2 …), use Home → Use First Row as Headers. This adds a PromotedHeaders step that shifts row 1 into the column-name row.
Change data types. Every column must carry the correct type: Whole Number, Decimal Number, Date, Text, etc. Power Query auto-detects types on import, but auto-detection is not always correct—especially for columns that contain leading zeros (zip codes, permit IDs) that should remain Text. Click the type icon in the column header and select the correct type. Changing a type adds a Changed Type step.
Remove duplicates. Select one or more key columns, right-click, and choose Remove Duplicates. This is row-level deduplication based on the selected columns. Always confirm the row count before and after; the status bar at the bottom of the editor shows the current row count.
Merge queries. Merging is Power Query's equivalent of a SQL JOIN. Use Home → Merge Queries, select the two queries, pick the matching column(s), and choose the join kind (Left Outer is the most common for building fact tables). After merging, expand the nested table column to surface the columns you need.
Null Values and Type Mismatches
A null in Power Query represents an absent value. Type mismatches occur when a column typed as, say, Whole Number contains a null or a text string such as "N/A". Power Query will flag these with an error indicator on the column header. Fix strategies include:
- Replace nulls with a default value (Transform → Replace Values, find null, replace with 0 or a sentinel).
- Remove rows that contain errors (Home → Remove Rows → Remove Errors).
- Correct the upstream source if the data should never be null.
The M Formula Language and the Advanced Editor
Every Applied Step is expressed in M, Power Query's functional formula language. You do not need to memorize M syntax, but you do need to be able to read and make small edits. The Advanced Editor (Home → Advanced Editor) shows the full M script for a query as a single function. A typical row-filter step looks like:
```
= Table.SelectRows(#"Changed Type", each [Budget] > 50000)
```
This keeps only rows where the Budget column exceeds 50,000. When you modify a step in the Advanced Editor, Power Query validates syntax on close; a red underline means a syntax error. Always verify the resulting row count in the status bar against an expected value before closing the editor.
File Path and Source Errors
A broken file path is one of the most common errors in shared .pbix files. If a CSV was connected from a personal desktop path (C:\Users\YourName\…) and the file is moved or the .pbix is opened on another machine, Power Query will show a DataSource.Error. Fix it by selecting the Source step in Applied Steps and editing the path in the formula bar or via Home → Data Source Settings.
Safety and Professional Practice
Data safety in analytics means protecting data integrity and, where applicable, personally identifiable information (PII). During lab:
- Never load real PII into a shared class database without authorization. Use only the provided sample datasets.
- Do not hard-code database passwords into M scripts or save them in plain text anywhere in the .pbix file.
- Save a backup of your .pbix before editing the Advanced Editor; a malformed M script can break a query and require a full rebuild of that query's steps.
- Label every Applied Step with a descriptive name so that a colleague—or your future self—can audit the transformation chain.
Verifying Your Work
Before submitting, check:
1. Zero error indicators on column headers and in the Queries pane.
2. All columns carry the correct data type icon.
3. The merged fact table row count matches the instructor's answer key exactly.
4. Applied Steps are named clearly and contain no unused or duplicate steps.
Key terms9
Materials: Power BI Desktop (free, current version), provided CSV and Excel sample datasets, access to class PostgreSQL instance (from Course 4), Microsoft Power Query documentation (docs.microsoft.com), lab log template
Check: Instructor reviews each student's .pbix file; pass requires correct data types on all columns, zero errors in Power Query pane, and a merged table matching the instructor's answer key row count (±0 rows). PL-300 mapping: Prepare the data domain.
Practice check8
Answer each question, then check it. Nothing is recorded; this is your own practice.
