Using the Power Query Add-In - Microsoft Business Intelligence Tools for Excel Analysts - page 137

To apply certain actions to multiple columns at one time, select the target columns
before right-clicking.

Tip

Table 7-1 shows what each action is meant to accomplish, and which actions are available with multi-
ple columns.

Many of the column actions have corresponding buttons on the Query Editor's Ribbon.

Tip

Table 7-1: Column-Level Actions

Action Purpose Available When

Selecting
Multiple Columns

Remove Removes the selected column from the Power Query data. Ye s

Remove Other
Columns

Removes all non-selected columns from the Power Query data. Ye s

Use First Row As
Headers

Replaces each table header name by the values in the first row of
each column.

No

Duplicate
Column

Creates a duplicate of the selected column as a new column placed
at the far right of the table. The name given to the new column is
Copy of X, where X is the name of the original column.

No

Split Column Splits the column into multiple columns either by a specified delim-

iter or by a specified number of characters.

No

Remove
Duplicates

Removes all rows from the selected column where the values duplicate
earlier values. The row with the first occurrence of a value is not removed.

Ye s

Remove Errors Removes rows containing errors in the selected column. Ye s

Replace Values Replaces one value with another value in the selected column. Ye s

Fill Down Fills empty cells in the column with the value of the first non-empty

cell above them.

Ye s

Change Type Changes the data type of the selected column to any of these types:

Binary, Date, Date/Time, Date/Time/Timezone, Duration, Logical,
Number, Text, Time, or Using Locale (localizes data types to the coun-
try you specify).

Ye s

Transform Changes the way values in the column are rendered. You can choose

from the following options: Lowercase, Uppercase, Capitalize Each
Word, Trim, Clean, JSON, or XML. If the values in the column are date/
time values, the options are Date, Time, Day, Month, Year, or Day of
Week. If the values in the column are number values, the options are
Round, Absolute Value, Factorial, Base-10 Logarithm, Natural
Logarithm, Power, or Square Root.

Ye s

Insert Custom
Column

Inserts a new column after the last column of the table. The values in
the new column are determined by selecting the type of column to
insert: Custom (you define the values) or Index (Power Query adds a
sequential list of numbers).

No

continued