Part V: Understanding Power Pivot and Power Query
TABLE 38.2 Table-Level Actions
Action Purpose
Copy Entire Table Copy the data within the current query to the Clipboard.
Use First Row as
Headers
Replace each table header name with the value in the first row of each
column.
Add Custom
Column
Insert a new column after the last column of the table. The values in the
new column are determined by the value or formula you define.
Add Column From
Examples
Create a custom column that combines data from other columns based on
a few examples that you provide. Like the Flash Fill feature in Excel, Power
Query’s smart detection logic infers transformation logic based on your
examples and then applies that logic to fill the new column.
Invoke Custom
Function
Insert a new column after the last column of the table and then run a user-
defined function for each row in the column.
Add Conditional
Column
Insert a new column after the last column of the table and then fill it with a
conditional if-then-else statement that you define.
Add Index Column Insert a new column containing a sequential list of numbers starting from
1, 0, or another specified value you define.
Choose Columns Choose the columns you want to keep in the query results.
Keep Top Rows Remove all but the top N number of rows. You specify the number
threshold.
Keep Bottom Rows Remove all but the bottom N number of rows. You specify the number
threshold.
Keep Range of
Rows
Remove all rows except the ones that fall within a range you specify.
Keep Duplicates Remove all rows where the values in the selected columns are unique,
enabling you to focus on the duplicate rows.
Keep Errors Remove all rows that do not contain an error. This allows for the quick fil-
tering of error values encountered during transformation.
Remove Top Rows Remove the top N rows from the table.
Remove Bottom
Rows
Remove the bottom N rows from the table.
Remove Alternate
Rows
Remove alternate rows from the table, starting at the first row to remove and
specifying the number of rows to remove and the number of rows to keep.
Remove
Duplicates
Remove all rows where the values in the selected columns duplicate earlier
values. The row with the first occurrence of a value set is not removed.
Remove Errors Remove rows containing errors in the currently selected columns.
