Part V: Understanding Power Pivot and Power Query
Action
Purpose
Available with
Multiple Columns?
Remove Errors Remove rows containing errors in the selected column. Yes
C hang e Ty p e Change the data type of the selected column. Yes
Transform Change the way values in the column are rendered. You
can choose from the following options: Lowercase,
Uppercase, Capitalize Each Word, Trim, Clean, Length,
JSON, and XML. If the values in the column are date/time
values, the options are as follows: Date, Time, Day,
Month, Year, or Day of Week. If the values in the column
are number values, the options are as follows: Round,
Absolute Value, Factorial, Base-10 Logarithm, Natural
Logarithm, Power, or Square Root.
Yes
Replace Values Replace one value in the selected column with another
specified value.
Yes
Replace Errors Replace unsightly error values with your own friendlier
text.
Yes
Group By Aggregate data by row values. For example, you can
group by state and either count the number of cities in
each state or sum the population of each state.
Yes
Fill Fill empty cells in the column with the value of the first
nonempty cell. You have the option of filling up or filling
down.
Yes
Unpivot
Columns
Transpose the selected columns from column-oriented to
row-oriented or vice versa.
Yes
Unpivot Other
Columns
Transpose the unselected columns from column-oriented
to row-oriented or vice versa.
Yes
Unpivot Only
Selected
Columns
Transpose the selected columns from column-oriented to
row-oriented or vice versa. This option also retains a col-
umns list in the current step so that the same set of col-
umns is unpivoted on future refresh operations.
Yes
Rename Rename the selected column to a name you specify. No
Move Move the selected column to a different location in the
table. You have these choices for moving the column:
Left, Right, To Beginning, and To End.
Yes
Drill Down Navigate to the contents of the column. This is used with
tables that contain metadata representing embedded
information.
No
Add as New
Query
Create a new query with the contents of the column. This
is done by referencing the original query in the new one.
The name of the new query is the same as the column
header of the selected column.
No
TABLE 38.1 (continued)
