Module 3 · Power Query and Collaboration · Power Query

Power Query and Google Sheets collaboration

Replace manual copy-paste with repeatable data preparation in Power Query, and collaborate safely in Google Sheets.

Intermediate50 min

Recommended first

This lesson builds on: PivotTables, cleaning, reconciliation and variance analysis.

Learning objectives

Import data from files and folders with Power Query
Apply and read transformation steps (types, filters, splits, unpivot)
Merge (join) and append (union) queries correctly
Refresh automatically and understand where refresh can fail
Use Google Sheets features for collaboration and their analytical equivalents

Why Power Query

Power Query (Data → Get Data in Excel; the same engine as Power BI's Power Query) records each transformation as a step. Next month you click Refresh instead of repeating 40 manual actions. Every step is visible, reversible and reviewable — exactly what manual cleaning is not.

Importing data

Data → Get Data → From File → From Text/CSV, From Workbook, From Folder (combine every file in a folder — ideal for monthly store files), From Database, From Web.

The Power Query editor shows a preview, and the Applied Steps pane lists what happened:

Source — the file
Promoted Headers — first row became headers
Changed Type — data types detected (always check this step!)

Trusting automatic type detection

Power Query guesses types from the first rows. A product code like 00123 can become the number 123; European dates like 04.03.2025 can be read as April 3rd with the wrong locale. Set types explicitly (right-click column → Change Type → Using Locale for dates).

Core transformations

Task	Where
Remove columns/rows, filter	Home tab, column filter arrows
Trim/clean/case	Transform → Format
Split column by delimiter	Transform → Split Column
Replace values	Transform → Replace Values
Fill down blanks	Transform → Fill → Down
Add calculated column	Add Column → Custom Column
Group By (aggregate)	Transform → Group By
Unpivot month columns into rows	Select ID columns → Transform → Unpivot Other Columns

The steps are written in the M language (View → Advanced Editor). You do not need to write M from scratch, but reading it helps:

let
    Source = Csv.Document(File.Contents("C:\data\orders.csv"), [Delimiter=",", Encoding=65001]),
    Headers = Table.PromoteHeaders(Source),
    Typed = Table.TransformColumnTypes(Headers, {{"order_date", type date}, {"discount_pct", type number}}),
    Completed = Table.SelectRows(Typed, each [status] = "completed")
in
    Completed

Merging and appending

Append stacks tables with the same columns (SQL UNION ALL) — e.g. January + February files.
Merge joins tables on key columns (SQL joins). Join kinds: Left Outer (default), Right Outer, Full Outer, Inner, Left Anti (rows in the first table without a match — the anti-join), Right Anti.

After a merge, expand only the columns you need. Merging a table with duplicate keys multiplies rows exactly like SQL fan-out — check row counts before and after.

Staging queries

Create a query per source with cleaning steps, set them to Connection Only, then build the final query by merging/appending the staged queries. Large models stay understandable.

Refresh and automation

Refresh All (Ctrl + Alt + F5) re-runs every query.
Query Properties → Refresh data when opening the file, Refresh every N minutes.
Files from a folder: dropping a new monthly file into the folder and refreshing loads it.
Fully unattended refresh (e.g. nightly) needs another tool: Power BI Service with a gateway, Power Automate, or a scheduled script (Module 14).

Refresh fails when file paths change, column names change in the source, or credentials expire. Name columns defensively and keep sources in stable locations (SharePoint/OneDrive rather than personal Desktops).

Google Sheets collaboration

Google Sheets offers most core functions (SUMIFS, XLOOKUP, UNIQUE, FILTER, PivotTables) plus collaboration features:

Need	Google Sheets
Simultaneous editing	Built in; see collaborators' cursors
History	File → Version history (name important versions)
Comments & tasks	Comment with `@name` to assign
Protect ranges	Data → Protect sheets and ranges
Import from other sheets	`=IMPORTRANGE(url, "Sheet1!A:F")`
SQL-like querying	`=QUERY(A1:E100, "select B, sum(E) where C = 'Grocery' group by B")`
Combine/clean	`SPLIT`, `REGEXREPLACE`, `ARRAYFORMULA`

QUERY brings SQL thinking into Sheets. On the Sales table: =QUERY(A1:E9, "select B, sum(E) group by B", 1) returns Baku 621 and Ganja 794.

Sharing settings

"Anyone with the link can edit" on a sheet containing customer emails or salaries is a data-protection incident. Share with named people, use view-only where possible, and protect formula ranges.

Practice

You have 12 monthly files with identical columns and want one table. Which Power Query operation?

Which merge (join) kind returns customers in the CRM table that have no match in the billing table?

Which changes are likely to make a Power Query refresh fail? Select all that apply.

Summary

Power Query turns manual preparation into refreshable, auditable steps; check types and locales.
Append = union; Merge = join (including anti-joins); watch row counts.
Refresh on open or on demand; unattended refresh needs the Service, Power Automate or scripts.
Google Sheets adds real-time collaboration, version history, IMPORTRANGE and QUERY; control sharing.

← Previous

PivotTables, cleaning, reconciliation and variance analysis

Power BI Desktop, Power Query and the star schema