Illustrative example
Some of the many errors found in source data include partial summarization of the data and grouping of some field headers. In this example, we are going to clean and transform data using various tools, including Unpivot.
You are given a dataset of sales per salesperson in two regions. The dataset includes a number of anomalies, including subtotals and headers in more than one row, as shown in Figure 5.11. You are asked to prepare a pivot table from the data.
(Sample data is available for download to give a more hands-on experience.)
Figure 5.11 – Sales by salesperson, by product, and by region
Use the following steps to load the table into Power Query, transform the data, and finally load it back into Excel as clean data ready for further analysis:
- The first step is to convert the data into a table.
Since the data has multiple header rows (see Figure 5.12), you will have to uncheck the option that says My...