What does Power Query do in the workflow?
explanation: This is exactly ETL: pulling from the source, transforming in the editor, and loading — all before the data leaves-Power Query.
Power Query — the engine room
All the ETL techniques, one by one, with a screenshot from the editor for each: data types, dates, splitting, conditional column, Merge and Append.
32Topics in the lesson
40Quiz questions
10Tasks in the exercise
21Screenshots
opening
How the lesson works
Three things to know before starting, then the lesson content, navigation between lessons, and preparation for practice.
01
Navigating between topics
the buttons Previous Fnext fixed at the top of the screen. You can also use the keyboard, swipe on your phone, or jump from the topic list.
02
Real screenshots
Almost every technique has a screenshot from the editor. Click it to open it full-size and see exactly where to click.
03
Questions and practice
the button for the test Leads to a 40-question quiz and a homework exercise — all about Power Query only.
The course's four lessons
The full track. You can jump to any lesson with a click.
Preparation for practice
The hands-on exercise at the end of the lesson. It's worth opening Power BI Desktop and the AdventureWorks file right now.
practice
AdventureWorks_Sales.xlsx
Seven sheets, 121,253 sales rows and 397 products — the same file from lesson 2, now from the ETL angle.
Chapter I · What Is Power Query
The engine that prepares the data
Power Query is the engine that performs ETL: pulls data from a source (Extract), cleans and shapes it (Transform), and loads it into the model (Load).
1
Almost no code
Every click generates a line of M code behind the scenes. You work with the mouse, and the code writes itself.
2
Full repeatability
You define it once. On every Refresh, all the cleaning reruns automatically on the new data.
3
Hundreds of sources
Excel, databases, websites, folders and API — and it all comes together in one place.
the moral: Power Query is the kitchen before the restaurant. You don't serve raw ingredients — first you clean, cut and arrange, and only cooked data reaches the plate, meaning the model.
Chapter I · What Is Power Query
Authentication and secure login
When connecting to a new source, Power Query asks for credentials once and saves them to the source — not to the file.
| Verification type | When to use |
|---|---|
| Anonymous | Public sources that don't require authentication — an open website or public API |
| Windows | Permissions of the logged-in user — organizational network and local files |
| Database / Basic | Username and password — typical for SQL Server, Oracle and databases |
| Organizational account | An organizational account (Microsoft / Google) in a secure sign-in window, without a password in the file |
| Web API / Key | An access key attached to the request header — for API-based services |
⚙
Where do you manage
File ← Options ← Data source settings. There you edit, delete or replace an expired password.
🔒
Privacy levels
Private / Organizational / Public — prevent sensitive data from leaking between sources during a merge.
⚠
Common mistake
Choosing the wrong authentication type = a connection error. Check with the system admin what the source requires.
Chapter I · What Is Power Query
An iron rule — read only
Power Query runs in an environment Read only. It pulls a copy of the data and formats it for the report — no operation here touches the source data.
✓
This screen is completely safe
Delete columns, filter rows, or replace text — no change will affect the Excel file or the SQL server. You can experiment without worry.
↧
Want to fix it from scratch?
A change meant to affect all the organization's systems must be made at the source itself — in the database or the file, not here.
Rule of thumb: Formatting for the report's sake ← Power Query. Fixing the truth ← the source.
Chapter II · The query editor
Applied Steps — the Time Machine
Power Query works like a smart macro recorder: every click is logged as a separate step. It's the complete history of the ETL process-ETL.
| step | What it did |
|---|---|
| Source · Navigation | Connecting to the source and pulling the right table |
| Changed Type | The system detected and changed data types — text, integer, date |
| Split Column by Delimiter | Splitting a column by a delimiter |
| Changed Type1 | An extra type change created automatically after the split |
| Added Conditional Column | The conditional column that was added — the currently selected step |
At the top of the panel: Properties ← Name — the query name is the table name as it will appear in the data model.
Chapter II · The query editor
Four things you must know about Applied Steps
✖
No Ctrl+Z
You can't undo an action the normal way. To fix a mistake, delete the step by clicking the ✖ that appears next to it.
⚙
Editing an existing step
Some steps have a gear icon. Clicking it reopens the step dialog — to change a value without redoing everything from scratch.
⏱
time travel
Clicking on any previous step shows exactly how the data looked before the following steps were applied.
🔎
Debugging
Thanks to time travel you can debug step by step and identify exactly where the data went wrong.
Chapter III · Dates and time
Changing date format — Using Locale
when: An American-style date (MM/DD/YYYY) might be interpreted backwards — day vs. month — or throw an error.
| step | What to click |
|---|---|
| 1 | In Power BI: Home ← Transform data (opening the editor) |
| 2 | Right-click on title The date column |
| 3 | Change Type ← Using Locale… |
| 4 | in the window: Data Type = Date · Locale = English (US) |
| 5 | click OK — a step was added in Applied Steps |
example: column Order Date: the value 01/07/2017 without a Locale is read as July 1; with English (US) it's correctly read as January 7.
Chapter III · Dates and time
Extracting date parts — Extract Date Parts
when: Segment by year, month or quarter without formulas — extract from an existing date column.
| step | What to click |
|---|---|
| 1 | choose the The date column (click on the name) |
| 2 | tab Add Column — to preserve the original |
| 3 | group Date & Time ← Date |
| 4 | Month ← Name of Month |
| 5 | A new column is created |
example: from01/07/2017 ← month name ← July.
Chapter IV · Numbers and Money
Currency format — Fixed Decimal Number
when: Fixed at 4 decimal places — precise for money, without rounding errors in calculations.
| step | What to click |
|---|---|
| 1 | choose the The money column (for example Standard Cost) |
| 2 | click on The type icon (ABC / 123) to the left of the column name |
| 3 | choose Fixed Decimal Number |
| 4 | alternative: Transform ← Data Type ← Fixed Decimal |
| 5 | The column is locked to 4 digits after the decimal point |
example: Standard Cost = 868.6342 ← Fixed Decimal (exact money).
Chapter IV · Numbers and Money
Rounding numbers — Rounding
when: To clean long numbers from a calculation and set how many digits remain.
| step | What to click |
|---|---|
| 1 | choose the The numeric column |
| 2 | tab Transform |
| 3 | group Number Column ← Rounding ← Round… |
| 4 | Type a number of digits: 2 |
| 5 | click OK |
example: 868.6342 ← round to 2 digits ← 868.63.
Chapter V · Text and Cleaning
Splitting a column — seven methods
when: To split a "packed" column into multiple columns — by delimiter, number of characters, position, or transition between character types.
| method | What it does |
|---|---|
| By Delimiter | Splitting by a chosen character — comma, dash, space. The most common and useful |
| By Number of Characters | Cutting every fixed number of characters — for segments and fixed-length codes |
| By Positions | Slicing at exact indices (character 3, character 8) — for a rigid structure without a delimiter |
| By Lowercase to Uppercase | Split at the transition from lowercase to uppercase — mountainBike ← mountain | Bike |
| By Uppercase to Lowercase | The opposite — transition from uppercase to lowercase: MACdonald ← MAC | donald |
| By Digit to Non-Digit | Split on the transition from digit to letter: 12345ABC ← 12345 | ABC |
| By Non-Digit to Digit | Split on the transition from letter to digit: PO9876 ← PO | 9876 |
example: SKU = FR-R92B-58 ← By Delimiter "-" ← FR · R92B · 58.
Chapter V · Text and Cleaning
Splitting by delimiter — step by step
| step | What to click |
|---|---|
| 1 | choose the the column (for example SKU) |
| 2 | Home or Transform ← Split Column |
| 3 | By Delimiter |
| 4 | choose Custom and type - |
| 5 | Split at: Each occurrence ← OK |
| separator | Where you encounter it |
|---|---|
| comma , | CSV lists, "last name, first name" |
| colon : | Times (14:30:00) or key-value (Category: Bikes) |
| equals = | URL addresses and parameters: ProductID=5432 |
| Semicolon ; | Exports from European systems, email lists in a single cell |
| profit Space | First and last name, breaking a sentence into words |
| tab Tab | Data copied from a TXT file or an old Excel file |
| Customized | Any character not in the list: hyphen, slash, underscore, or a whole word |
Chapter V · Text and Cleaning
Text formatting — Format
when: Cleaning and standardizing text. One menu that consolidates seven common operations.
| operation | What it does |
|---|---|
| lowercase | lowercase — for standardizing email addresses |
| UPPERCASE | Uppercase letters — for country and currency codes (USD, ILS) |
| Capitalize Each Word | First letter capitalized in every word — for names and cities |
| Trim | Removing edge spaces only — from the start of the string and its end |
| Clean | Cleaning hidden non-printing characters (Enter, Tab) — required when importing from free text |
| Add Prefix | Fixed text before the value: 123 ← ID-123 |
| Add Suffix | Fixed text after the value: liran ← liran@company.com |
Why Trim is critical: the value "Apple " with a trailing space isn't identical to "Apple". In merges and relationships, that's the difference between a match and an orphan row.
Chapter V · Text and Cleaning
Replacing values — Replace Values
when: Fix incorrect values or merge different spellings into the same value.
| step | What to click |
|---|---|
| 1 | Right-click on the column header |
| 2 | Replace Values… |
| 3 | Top: the value to replace · Bottom: the new value |
| 4 | In Advanced options you can restrict to a full cell match only |
| 5 | OK |
| case | example |
|---|---|
| Merging spellings | "USA" · "U.S.A" · "United States" ← a single value |
| Cleaning symbols | Removing ₪ or % stuck in a numeric column |
| Handling empties | replacing null with the text "unknown" before loading into the model |
Chapter VI · Adding columns
Conditional column — Conditional Column
when: IF…THEN…ELSE logic in a graphical interface — a new value based on a condition, without writing code.
| step | What to click |
|---|---|
| 1 | Add Column ← Conditional Column |
| 2 | Type a name: Transaction classification |
| 3 | If [Sales Amount] > 5000 Then "Large" |
| 4 | Add Clause ← Else If > 1000 Then "Medium" |
| 5 | Else: "small" ← OK |
Chapter VI · Adding columns
The waterfall principle
when: Classifying continuous data into categories (Bucketing) — and understanding how Power Query reads the conditions.
≡
Interface instead of code
Build IF…THEN…ELSE with clicks, without writing a single line of M.
⚠
No Between
In a window there's no "between" operator. Anyone coming from SQL or Excel looks for it — and doesn't find it.
✓
The waterfall solves this
The conditions are read from top to bottom. The first one that holds decides, and a row is checked only if all the ones before it failed.
!
Order is everything
always from high to low. In reverse order, the high values would \"get caught\" early and the \"high\" would never exist.
example: List Price > 70 high · ≥50 medium · ≥30 low · otherwise very low.
Chapter VI · Adding columns
The waterfall rule — step by step
| step | the condition | what happens |
|---|---|---|
| 1 | If > 70 ← "High price" | The engine goes row by row; 70.01 and above gets "High" and doesn't check the rest |
| 2 | Else If ≥ 50 ← "Medium price" | We only get here if step 1 fails — that's how the range 50–70 is actually created without Between |
| 3 | Else If ≥ 30 ← "Low price" | Only records below 50 reach here: the condition catches 30.01–49.99 |
| 4 | Else ← "very low price" | The safety net: everything that's left, including 0 and negatives, automatically gets this condition |
example: List Price = 1431.5 ← high price · 9.5 ← very low price.
Chapter VI · Adding columns
Index Column — Index Column
when: to create a unique running identifier for each row — useful as a surrogate key, for stable sorting, or for finding duplicates.
| step | What to click |
|---|---|
| 1 | Add Column ← Index Column |
| 2 | choose From 0 or From 1 (or Custom with your own step) |
| 3 | The column is added at the end of the table and numbers the rows by their current order |
Note: the index is determined by Row order at the moment of creation. If you sort before that, the numbering will change accordingly.
Chapter VII · Filtering and Types
Number filtering — Number Filters
when: To reduce rows by numeric rules — dynamically, in a way that survives data updates.
| option | What it does |
|---|---|
| Greater / Less Than | "Floor" or "ceiling": only prices ≥50, or only items cheaper than 20 |
| Equals / Does Not Equal | An exact match to a value — for example filtering out a price of 0 |
| Between | Here Between does exist — in the filter tool, unlike the conditional column window |
| Manual marking in checkboxes | Convenient, but blocks new values that come in on the next refresh |
Manual selection vs. a logical condition: the checkboxes relate to values that exist now. A logical condition will keep catching new values too.
example: Between 30…70 on List Price ← only the mid-range prices.
Chapter VII · Filtering and Types
Changing data type — Change Type
when: The most important operation. A number defined as text can't be summed or averaged.
| step | What to click |
|---|---|
| 1 | click on The type icon (ABC / 123) to the left of the column name |
| 2 | choose Whole Number (or Decimal / Text / Date) |
| 3 | If you're asked — Replace current Instead of Add new step |
| 4 | Now you can perform aggregations on the column |
example: Order Quantity With an icon ABC (text) ← Whole Number.
Chapter VIII · Combining tables
Merge — opening the merge
when: The equivalent of JOIN or VLOOKUP. Connects Sales to Product by ProductKey.
| step | What to click |
|---|---|
| 1 | stand at Sales ← Home ← Merge Queries |
| 2 | The Merge window opens — at the top already appears Sales |
| 3 | In the dropdown menu the bottom Select the second table: Product |
| 4 | Both tables are shown one above the other — ready to define the shared key |
Chapter VIII · Combining tables
Merge — the shared key
when: Here you define by what The tables connect. Without it there's no merge.
| step | What to click |
|---|---|
| 1 | Click on a column ProductKey in the top table — it's colored |
| 2 | click on ProductKey in the bottom table — is also colored |
| 3 | validation: Below appears 121253 of 121253 rows matched |
| 4 | Join Kind = Left Outer (default) ← OK |
The match rate is the quality gauge: 121,253 out of 121,253 means every sales row found a product. A lower number signals orphaned keys or spaces in the text.
Chapter VIII · Combining tables
Merge — the new Table column
when: The merge was created, but the data is still folded inside a single column.
| what happened | Where do you see |
|---|---|
| A new column named Product was created | At the end of the table — every cell says Table |
| a step was added | Applied Steps ← Merged Queries |
| The code written behind the scenes | Table.NestedJoin(...) |
| What to do now | Expand to see the data — in the next step |
Chapter VIII · Combining tables
Merge — Expand and pulling the columns
| step | What to click |
|---|---|
| 1 | Click the Expand icon (⇆) at the top of the column Product |
| 2 | we choose only The columns you need — for example List Price andCategory |
| 3 | Cancel the Use original column name as prefix for short names |
| 4 | OK |
Don't select everything. Every unnecessary column stays in memory and weighs down the model.
Chapter VIII · Combining tables
Merge — the final result
- The Product column was replaced withReal columns.
- Every sales row now has a category and a price — Without manual VLOOKUP.
- On every Refresh the merge will automatically re-run on the new data.
A question worth asking: do you really need the merge? In a proper star schema, a relationship between the tables does the same job without bloating the fact table.
Chapter VIII · Combining tables
Append — concatenating queries
when: the equivalent of UNION. To chain tables with identical structure Into one long table.
| step | What to click |
|---|---|
| 1 | Working on the first query ← Home ← the arrow next to Append Queries |
| 2 | we choose Append Queries as New — keeps the source clean and creates a third query |
| 3 | we choose Two tables or Three or more and the table to concatenate |
| 4 | OK — the engine concatenates them one under the other |
example: Sales_Jan + Sales_Feb (date · item · quantity) ← ) → one continuous table. This is the scenario of merging monthly files.
Chapter VIII · Combining tables
The NULL mystery — Append vs. SQL
This is the first question everyone coming from SQL asks: why were columns full of nulls created?
≡
Equivalent to UNION ALL, not UNION
Append adds the all the rows one below the other, without removing duplicates.
!
Merges by name, and is case-sensitive
Unlike SQL, which joins by position, Power Query joins Solely by the column name — and case-sensitive. City and city will be considered two different columns.
gold tip: Before Append make sure the column names are 100% identical, including case and spaces. Not identical? Do a Rename to line them up before appending.
Chapter IX · Summary and Self-Check
The techniques table — everything in one place
Everything we covered, by location in the interface.
| technique | where |
|---|---|
| Changing the date format | Right-click ← Change Type ← Using Locale |
| Extracting date parts | Add Column ← Date |
| Currency format | type icon / Transform ← Fixed Decimal |
| Rounding numbers | Transform ← Rounding |
| Splitting a column | Home / Transform ← Split Column |
| Text formatting | Transform ← Format |
| Removing spaces | Transform ← Format ← Trim |
| Replacing values | Right-click ← Replace Values |
| A conditional column | Add Column ← Conditional Column |
| An index column | Add Column ← Index Column |
| Filtering numbers | Filter arrow ← Number Filters |
| Changing data type | The type icon |
| Merging tables | Home ← Merge Queries |
| Concatenating tables | Home ← Append Queries |
Chapter IX · Summary and Self-Check
What we learned — eight points
01
ETL before the model
Power Query pulls, cleans and loads. What reaches the model is already clean.
02
Read only
No action in the editor touches the source file or the server.
03
Applied Steps
Every click is a step. There's no Ctrl+Z — you delete a step, edit a step, and go back in time.
04
Types first
A number loaded as text can't be summed. Change Type is the first step.
05
Using Locale
An American-format date is parsed reversed without a locale setting.
06
The waterfall principle
There's no Between in a conditional column — the top-to-bottom order creates the ranges.
07
Merge vs. Append
Merge adds columns side by side (JOIN), Append adds rows below (UNION ALL).
08
The column name determines
In Append the match is by name and case-sensitive; in Merge the matched row is the quality measure.
40 questions, all on Power Query — no modeling and no DAX
Chapter IX · Summary and Self-Check
Quiz — 40 questions on Power Query
One question at a time, four options, immediate feedback with an explanation. All the questions deal only with the Power Query editor.
An action you performed in the editor — how does it affect the source file?
explanation: The editor runs in a read-only environment. You can experiment without any risk to the source data.
In the Advanced Editor, M code opens. What does the structure contain let … in?
explanation: Between let and-in a step is defined after another, and each step gets a name; after in you specify which step is the query's output.
What gets generated behind the scenes with every click in the Power Query editor?
explanation: The editor is a recorder: you work with the mouse, and the M code is written on its own and can be viewed in the Advanced Editor.
What happens to the cleaning steps on every Refresh?
explanation: Define once, and the process repeats itself on every refresh.
You want another query that continues from the existing steps, and updates with them. What do you choose?
explanation: Duplicate creates a disconnected copy; Reference continues from the original query, and any change to it also flows to the new query.
You connected to a new source and were asked for credentials. Where are they saved?
explanation: The details are saved at the source level, and are managed in File ← Options ← Data source settings.
What kind of authentication suits a public source like an open website or a permission-free API?
explanation: A source that doesn't require authentication connects anonymously; Power Query remembers the choice for future connections to the same source.
Which authentication type in the editor uses the permissions of the user logged into the computer?
explanation: typical for organizational network folders and local files — Power Query authenticates using the already-logged-in username.
What is the role of Privacy Levels?
explanation: Every source is marked Private, Organizational, or Public, and that affects what's allowed to be combined.
A column has one value with empty cells below it that belong to the same group. How do you fill them in?
explanation: Fill Down copies the last non-empty value downward — a classic solution for reports exported from Excel with merged cells.
What gets recorded in the Applied Steps list?
explanation: The editor works like a recorder: every click becomes a separate step in the list.
You made a mistake. How do you undo it in the editor?
explanation: There's no regular undo. The only way is to delete the step from the list.
You want to change a value defined in a previous step, without redoing everything. What do you do?
explanation: Some steps have a gear icon that lets you edit them in place.
A column has duplicate values and you want to keep one row per value. What do you do in the editor?
explanation: The operation keeps the first occurrence of each value and removes the rest — based only on the selected columns.
Which step is almost always created automatically right after connecting to the source?
explanation: The system guesses data types for each column and records it as a step.
Why is it worth checking the automatic type-detection step?
explanation: Automatic detection is convenient, but it's a guess — so it requires verification.
A numeric column was loaded as text. What is blocked in the editor itself?
explanation: The ribbon offers operations by column type. As long as it's text, the number tools are disabled.
Where's the fastest place to change a column's data type?
explanation: The (ABC / 123) icon opens the list of all types.
The column shows 01/07/2017 and the date is parsed backward. What's the solution?
explanation: Locale tells the engine which convention to use to read the date.
The first row in the table contains the column names, but it was loaded as a data row. What do you do?
explanation: The action promotes the first row to headers and adds a step to Applied Steps. There's also a reverse action — Use Headers as First Row.
You want a column with the month name from an existing date column. What's the right way?
explanation: Work in Add Column to preserve the source column.
Why is Add Column preferred over Transform when extracting date parts?
explanation: You usually want to keep the full date alongside its parts.
Which data type suits a money column that requires precision in calculations?
explanation: It fixes four digits after the decimal point and prevents cumulative rounding errors.
Want to shorten long numbers to two digits after the decimal point. What do you use?
explanation: Round takes the desired number of digits; there's also Round Up and Round Down.
The SKU column contains FR-R92B-58 and you want to split it into three columns. What's the way?
explanation: This is the common splitting method: you choose the delimiter character and split at every occurrence of it.
The value mountainBike should split into mountain and Bike. Which method?
explanation: The split happens at the transition point from a lowercase to an uppercase letter.
When splitting a column: the value PO9876 should split into PO and 9876. Which Split Column method fits?
explanation: The split point is the transition from a letter to a digit, hence Non-Digit to Digit — without specifying a delimiter or position.
What purpose does the Trim operation serve?
explanation: The value "Apple " with a trailing space is not the same as "Apple" — and that breaks matches in a merge.
Hidden Enter and Tab characters got in from a free-text import. What cleans them up?
explanation: Clean removes non-printable characters, while Trim only handles leading/trailing spaces.
You want to merge "USA", "U.S.A" and "United States" into one value. What's the tool?
explanation: Replace every spelling with a consistent value, before the data reaches the model.
What does a conditional column enable?
explanation: A new value is set based on a condition, without writing a line of code.
Looking for a "between" operator in the conditional column window and not finding it. What's correct?
explanation: The conditions are read top to bottom, and each row is checked only if the previous ones failed — this is how a range is created.
In a conditional column in the editor — in what order should the condition rows be written?
explanation: Conditions are checked top to bottom, so in reverse order the lower condition would catch everything and the higher condition would never be met.
What's the role of the Else row at the end of the conditional column?
explanation: Without Else, rows that didn't match will get a blank value.
Where does Between exist?
explanation: Number Filters includes Between — unlike the conditional column.
What does Merge Queries do?
explanation: This is the equivalent of VLOOKUP or JOIN: the data comes alongside, as additional columns.
In the Merge window the line "121253 of 121253 rows matched" appears. What does it mean?
explanation: This is the most important quality check in a merge; a lower number indicates orphaned keys.
After a Merge, a column was created where every cell says Table. What do you do?
explanation: The data is nested inside the column until expanded, and it's recommended to bring in only the columns you actually need.
You appended two tables and columns full of null were created. Why?
explanation: Unlike SQL, which joins by position, here City and city are two different columns. Append is equivalent to UNION ALL and does not remove duplicates.
You haven't answered questions yet — 0 out of 40
Now to the editor itself, on the AdventureWorks file
Chapter X · Hands-on Practice
Homework — 10 tasks with solutions
All the tasks in the Power Query editor on AdventureWorks. After each task there's a full solution with the expected number.
Before we begin
AdventureWorks_Sales.xlsx
Home ← Transform data opens the editor. Nothing done here touches the source file.
Progress tracking
0 out of 5
1 Opening the editor and checking types
Home ← Transform data. Go through the seven queries and check the type icon of every column.
| step | what to do |
|---|---|
| 1 | choose the Sales and check the Order Quantity — should be an integer |
| 2 | check the Sales Amount — should be a decimal |
| 3 | Mark for yourself every column that was marked ABC and should be a number |
| table | rows |
|---|---|
| Sales | 121,253 |
| Product | 397 |
| Customer | 18,485 |
| Date | 1,461 |
| Reseller | 702 |
| Sales Territory | 11 |
| Sales Order | 121,253 |
✓ The row count at the bottom of the editor must match the table. If not — a filter step was added by mistake.
2 Fixing the date format
in the table of Date Make sure the date column is read correctly.
| step | what to do |
|---|---|
| 1 | Right-click the date column header |
| 2 | Change Type ← Using Locale… |
| 3 | Data Type = Date · Locale = English (US) ← OK |
✓ The date range should be 1.7.2017 to 30.6.2021 — 1,461 consecutive days.
3 Extracting the month name
Add a column with the month name from the date column.
| step | what to do |
|---|---|
| 1 | Select the date column |
| 2 | Add Column ← Date ← Month ← Name of Month |
✓ A new column is created with January through December, without affecting the source column.
4 Money format and rounding
in the table of Product: Set the Standard Cost as money, and round List Price.
| step | what to do |
|---|---|
| 1 | Icon of the type of Standard Cost ← Fixed Decimal Number |
| 2 | choose the List Price ← Transform ← Rounding ← Round… ← 2 ← OK |
✓ The value 868.6342 becomes 868.63 after rounding to two decimal places.
5 Splitting the SKU column
in the table of Product split the SKU into three parts.
| step | what to do |
|---|---|
| 1 | Choose the column SKU |
| 2 | Transform ← Split Column ← By Delimiter |
| 3 | Custom ← type - ← Split at: Each occurrence ← OK |
✓ FR-R92B-58 splits into three columns: FR · R92B · 58. Note that a Changed Type step was added automatically.
6 Text cleaning
Make sure there are no leading/trailing spaces in the text columns of Product.
| step | what to do |
|---|---|
| 1 | Choose the column Product (product name) |
| 2 | Transform ← Format ← Trim |
| 3 | Repeat this for Category |
✓ Trim removes spaces only from the start and end of the value. If there are hidden characters — add Clean.
7 Conditional column — price classification
Create a column that classifies the List Price To four levels.
| step | what to do |
|---|---|
| 1 | Add Column ← Conditional Column · Name: Price classification |
| 2 | If List Price > 70 Then "High price" |
| 3 | Else If ≥ 50 Then "medium price" · Else If ≥ 30 Then "Low price" |
| 4 | Else ← "very low price" ← OK |
✓ Descending order is critical: 1431.5 gets "high", 9.5 gets "very low". In reverse order everything would fall into one category.
8 Merging Sales with Product
Bring the category and price of the product to every sales row.
| step | what to do |
|---|---|
| 1 | stand at Sales ← Home ← Merge Queries |
| 2 | we choose Product In the bottom menu |
| 3 | click on ProductKey In both tables |
| 4 | Check the matching row ← Join Kind = Left Outer ← OK |
| check | result |
|---|---|
| Matched rows | 121,253 out of 121,253 |
| The column created | Product — every cell says Table |
| Added step | Merged Queries |
✓ A full match means there are no orphan keys. A lower number would signal a data problem.
9 Extending the merge
Pull only the two columns you need.
| step | what to do |
|---|---|
| 1 | Click the Expand icon at the top of the Product |
| 2 | mark only List Price andCategory |
| 3 | cancel the Use original column name as prefix ← OK |
✓ Now every sales row has a category and a price — no manual VLOOKUP, and it reruns on every refresh.
10 An Append experiment
Concatenate two tables and check what happens when the column names aren't identical.
| step | what to do |
|---|---|
| 1 | duplicate the Reseller (right-click ← Duplicate) and in the copy, change one column name to lowercase |
| 2 | Hover over the source ← Home ← Append Queries ← Append Queries as New |
| 3 | Choose the copy ← OK |
✓ The column whose name differs will be created twice, and each one will be filled with null in half the rows. This is proof that matching by name is case-sensitive.
Chapter XI · What's next
Resources and next steps
Go deeper into Power Query
You can also navigate with the buttons at the top of the screen, keyboard arrows, and on phone with a swipe