Excel PivotTables are an essential tool for data analysis and reporting. They help to summarize and analyze large datasets efficiently. However, sometimes, you might encounter a rather peculiar situation where you have multiple rows that equal one data point. In this article, we will explore how to create PivotTables with such "long" data format using Power Query.
Prerequisites
To follow along with this article, ensure you have the following prerequisites:
- Microsoft Excel
- Microsoft Power Query
Understanding the Data
Let's take a look at the dataset in the provided image (
). This dataset has multiple rows representing the same data point.
Data Preparation
To create a PivotTable from this dataset, we need to transform it using Power Query. Follow these steps:
- Load the data into Power Query by clicking on "Data" > "Get Data" > "From File" > "From Workbook" and selecting the Excel file.
- Once the data is loaded, click on "Home" > "Transform Data" to open the Power Query Editor.
- Rename the table to something like "SourceTable" by clicking on the tab name at the bottom of the editor.
Transforming the Data
To transform the data, follow these steps:
- Click on "Add Column" > "Custom Column" and name it "Combined".
- In the "Custom Column" formula bar, enter the following formula:
let
CombineText = (text as text) =>
Text.Combine(Text.Delimited(text, Char.Comma), " "),
#"Added Custom" = Table.AddColumn(SourceTable, "Combined", each CombineText([Column1] & " " & [Column2]))
in
#"Added Custom"
This formula concatenates the values in Column1 and Column2 with a space in between and adds the result to a new column called "Combined".
Creating the PivotTable
Now that we have transformed the data, we can create the PivotTable:
- Click on "Home" > "Close & Load" to close the Power Query Editor and load the transformed data back into Excel.
- Select the data and click on "Insert" > "PivotTable" to create a new PivotTable.
- In the "Create PivotTable" dialog box, ensure the data range is correct and select the location where you want to place the PivotTable.
- Drag the "Combined" column to the "Rows" area and the column you want to summarize to the "Values" area.
In this article, we explored how to create a PivotTable from a dataset with multiple rows that equal one data point using Power Query. By transforming the data using Power Query and then creating a PivotTable, we were able to summarize and analyze the data efficiently.
References