Menu
×
   ❮   
HTML CSS JAVASCRIPT SQL PYTHON JAVA PHP C C++ C# AWS W3.CSS HOW TO BOOTSTRAP REACT MYSQL JQUERY EXCEL XML DJANGO NUMPY PANDAS NODEJS DSA TYPESCRIPT ANGULAR ANGULARJS GIT POSTGRESQL MONGODB ASP AI R GO KOTLIN SWIFT SASS VUE GEN AI SCIPY CYBERSECURITY DATA SCIENCE INTRO TO PROGRAMMING HTML & CSS BASH RUST TOOLS MATH

Power BI Power Query Columns


Share

Shape the Columns

The first job in most cleanups is to get the columns right. In this chapter you:

  • Remove the Notes column
  • Rename Qty to Quantity and Price to UnitPrice
  • Learn how to move columns
  • Add a Sales column with a formula

This page continues the cleanup from Power Query Editor.

Open pq_cleanup.pbix, the report you saved there, select Home > Transform data, and select the last step of the poke_mart_raw_2024 query, Changed Type.

This is where you start.

The query has 1,285 rows and seven columns:

123OrderIDDATEOrderDateABCLocationABCProduct123Qty123PriceABCNotes
10011/1/2024Lilycove City - HoennHyper Potion11200
10021/1/2024Cerulean City - Kantopoke ball19200
10031/2/2024Lilycove City - Hoenn  Great Ball 13600
10041/2/2024Cerulean City - KantoAntidote9100
10051/3/2024Violet City - JohtoPotion19300
10061/3/2024Slateport City - HoennParalyze Heal1200
.....................

Remove a Column

The Notes column is empty in most rows.

Only 45 of the 1,285 rows have a note, such as "phone order" or "gift wrap".

The report does not need it.

  1. Select the Notes column header.
  2. On the Home tab, select the arrow under Remove Columns.
  3. Select Remove Columns.

The Home tab of the Power Query Editor with the Notes column selected and the Remove Columns dropdown open, showing Remove Columns and Remove Other Columns

Notes is gone, and a new step, Removed Columns, appears in the Applied Steps list.

You can also right-click the header and select Remove.

To remove several columns at once, hold Ctrl and click each header first.

Remove Columns or Remove Other Columns?

The Remove Columns dropdown has two commands:

CommandWhat it doesUse it when
Remove ColumnsRemoves the selected columnsYou want to drop a few columns and keep most
Remove Other ColumnsKeeps the selected columns and removes all the othersYou want to keep a few columns and drop most

There is one more difference.

Remove Other Columns remembers the columns you keep, and Remove Columns remembers the columns you remove.

So if the source ever gets a new column, Remove Other Columns drops it, while Remove Columns lets it through into your report.

Here you want to drop one column out of seven, so Remove Columns is the quicker choice.

Home > Choose Columns does the same job with a list of check boxes, which is handy for wide tables.


Rename Columns

Qty and Price are short, but not very clear.

The column names you choose here become the field names in your report, so give them clear names.

  1. Double-click the Qty header.
  2. Type Quantity and press Enter.
  3. Double-click the Price header.
  4. Type UnitPrice and press Enter.

A step called Renamed Columns appears.

When you rename two columns right after each other, Power Query puts both renames in the same step.

You can also right-click a header and select Rename..., or use Transform > Rename.

Two columns cannot have the same name.

If you try, Power Query shows a Column Name Conflict message, and you must choose another name.



Move Columns

To move a column, drag its header to a new place.

Or select the column and use Transform > Move, which moves it one place left or right, or to the beginning or the end.

The Poke Mart columns are already in a good order, so you do not need to move anything.

If you try it out, delete the Reordered Columns step afterwards: select the X next to it in Applied Steps.

That way your query stays the same as in this tutorial.

The column order in Power Query matters less than you might think.

The Data pane of your report lists the fields in alphabetical order anyway.


Add a Custom Column

The file has the quantity and the unit price of each order, but not the sales amount.

You can calculate it with a custom column:

Sales = [Quantity] * [UnitPrice]
  1. On the Add Column tab, select Custom Column.
  2. In New column name, type Sales.
  3. Click in the Custom column formula box, after the equal sign. The box always starts with = , and you cannot delete it, so do not type an equal sign yourself.
  4. In the Available columns list, double-click Quantity. Power Query writes [Quantity] in the formula.
  5. Type * (a space, an asterisk and a space).
  6. Double-click UnitPrice in the list.
  7. Check the message at the bottom of the dialog: No syntax errors have been detected.
  8. Select OK.

The Custom Column dialog in Power Query with the new column name Sales, the formula [Quantity] * [UnitPrice], the Available columns list, and the message No syntax errors have been detected

The formula box now holds this:

= [Quantity] * [UnitPrice]

A column name in square brackets means "the value in this column, in the current row".

Power Query runs the formula once for each row.

Note: Power Query formulas are case-sensitive.

[quantity] with a small q is not the same as [Quantity], and gives an error.

Double-clicking the column names in the list avoids typing mistakes.

This is why you renamed the columns first: the formula uses the new, clear names.


The Result

A new step, Added Custom, is added, and the new Sales column appears at the right end of the table:

123OrderIDDATEOrderDateABCLocationABCProduct123Quantity123UnitPriceABC123Sales
10011/1/2024Lilycove City - HoennHyper Potion112001200
10021/1/2024Cerulean City - Kantopoke ball192003800
10031/2/2024Lilycove City - Hoenn  Great Ball 136007800
10041/2/2024Cerulean City - KantoAntidote9100900
10051/3/2024Violet City - JohtoPotion193005700
10061/3/2024Slateport City - HoennParalyze Heal1200200
.....................

Check the first rows: 19 Poke Balls at 200 each make 3800, and 13 Great Balls at 600 each make 7800. The query still has 1,285 rows.

Look at the icon in the Sales header: ABC123.

That is the type Any.

In Power BI Desktop, the Custom Column dialog has no box for the data type, so a new custom column has no type yet.

You fix that in the next chapter, Data Types.

Select the Added Custom step and look at the formula bar.

It shows something similar to this:

= Table.AddColumn(#"Renamed Columns", "Sales", each [Quantity] * [UnitPrice])

In words: take the table from the step Renamed Columns, and add a column called Sales, with each row's Quantity times its UnitPrice.

Your Applied Steps are now: Source, Promoted Headers, Changed Type, Removed Columns, Renamed Columns and Added Custom. Good job!

Select Close & Apply, then File > Save to save pq_cleanup.pbix.

The next page continues from here.



Free account

Track your progress

XP earned 0 Day streak 0 Spaces 0 League —

New

Learn in Adventure Mode W3Schools Adventure App

Coding fundamentals as bite-sized lessons and challenges.

×

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
sales@w3schools.com

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail:
help@w3schools.com

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookies and privacy policy.

Copyright 1999-2026 by Refsnes Data. All Rights Reserved. W3Schools is Powered by W3.CSS.