Part V: Understanding Power Pivot and Power Query
FIGURE 40.1
This data can be used as the source for various levels of aggregated analysis.
To see what this means, take a moment to follow these steps:
- Open the Sales By Employee.xlsx sample file.
- Select any cell inside the table and then click Data ➪ From Table/Range. Power
Query will open the Power Query Editor to show you a table that looks similar to
Figure 40.1.
- Click the Filter drop-down for the Market field, and filter out the Canada mar-
ket (remove the check next to Canada).
- Select the Last_Name and First_Name fields, right-click either column head-
ing, then select Merge Columns.
- Use the Merge Columns dialog box to create a new Employee field, joining
Last_Name and First_Name separated by a comma (see Figure 40.2).
- Click the Group By command on the Transform tab to display the Group By dia-
log box. The goal is to group by the Employee field to get the Sum of Sales Amount.
Name the new aggregated column Revenue.
Figure 40.3 illustrates the completed Group By dialog box.
At this point, you’ve successfully created a view that shows total revenue by
employee. As you can see in Figure 40.4, the query steps include all of the prepara-
tion work you did before grouping.
