To simplify your data and aggregate them together we can Pivot the huge data table using Power Query Editor in Power BI. This amazing feature of Power Query has hundreds of benefits. We can use it for a data source that has too many numbers of columns and rows.
Data Analytics Tricks in Power BI – Guidebook
You too can analyze data like a Data Scientist. No coding needed. No statistics needed.
Analyze & Visualize data using Power BI. (23 tricks in one book)
Create Pivot Columns Using Power Query Editor
Let’s take an example where we have a data table showing data Sales Quarter and multiple sales values for each quarter. We want to sum them all per quarter. This can be done easily with below-given steps.
Step 1: Select the column in Power Query
After you load the data in Power Query, select the column to apply the Pivot Column feature as shown in the picture below.
Step 2: Create Pivot Column
Once you select the column that you want to Pivot, click on the options as shown below
Transform > Pivot Column
After you click on the Pivot Column button, the Pivot Columns Window gets open. Here, you have to specify the Values Column name. In this case, the value column is the Sales column.
Also, go to the Advanced options and select the function as we have selected Sum function here and click on OK.
Step 3: Close & Apply
Now, you can close and apply the changes after you observe the transformations that take place in your data as shown below.
You now have your new table with a total of each sales quarter. That’s why they call it POWER QUERY!!!
Data Analytics Tricks in Power BI – Guidebook
You too can analyze data like a Data Scientist. No coding needed. No statistics needed.
Analyze & Visualize data using Power BI. (23 tricks in one book)