Search

DataChant

Chants of big data & bigger insights

Category

Unpivot – The definitive guide

Split and Unpivot Comma-Separated Values

(By: Gil Raviv. Originally published here)

By now, if you read my previous posts in the series The Definitive Guide to Unpivot in Excel (all posts here), you will feel confident with the following challenge:

We have a table of event names, dates and comma-separated participant names, and we want to build a PivotTable that will show us the participants and their count of events (as the following screenshot shows).

In today’s post we will examine the intuitive solution that includes a split and unpivot transformations, but then we will see a much better and scalable way to achieve our goal.

Let’s start with a walkthrough of the intuitive solution.
Continue reading “Split and Unpivot Comma-Separated Values”

Advertisements

Unpivot ANY nested table and load to Power BI Desktop

(By: Gil Raviv. Originally published here).

Earlier today I shared with you a function query here that unpivots ANY nested table into Excel and transforms summarized tables into a PivotTable. This post will show you how to import the function query and use it in Power BI Desktop on any table you load from Excel.

It is a common scenario to have nested/summarized tables in Excel that you need to unpivot before you build the dashboard with Power BI Desktop. With the function query which is described here, you can load ANY nested table to the Power BI Desktop and transform it to a format you can start working with.
Continue reading “Unpivot ANY nested table and load to Power BI Desktop”

Transform ANY nested table to Pivot Table with function query

(By: Gil Raviv. Originally published here).

Get ready to be amazed 🙂

This is the fourth post in the series The Definitive Guide to Unpivot with Power Query in Excel.

In this series we walk you through one of the coolest data transformation features in Excel – The Unpivot transformation. Powered by Power Query technology, the Unpivot transformation is available for everyone using the new Get & Transform section of the Data tab in Excel 2016, or as an Add-in in previous versions of Excel.

In my last post here, I showed you how to transform a nested table into a PivotTable. In today’s post we will move one step further, and learn how to transform ANY nested table into a PivotTable. Why ANY? Because no matter how many fields you have as nested rows and columns, you will be able to transform it to a PivotTable if you follow today’s walkthrough.

We will start with a specific scenario, and then share with you a more generic function query which uses Power Query expression (M) to address any type of nested table.

Let’s start with a table that uses Continent, Country and City as row fields, and School Type, Gender and Class as column fields:

Our goal is to unpivot & transform this 3×3 dimension table above the following table which can be then used by PivotTables and PivotCharts:

 

Let’s start

Continue reading “Transform ANY nested table to Pivot Table with function query”

Transform a nested table to PivotTable

(By: Gil Raviv. Originally published here).

This is the third post in the series The Definitive Guide to Unpivot with Power Query in Excel. In this series we walk you through one of the coolest data transformation features in Excel – The Unpivot transformation. Powered by Power Query technology, the Unpivot transformation is available for everyone using the new Get & Transform section of the Data tab in Excel 2016, or as an Add-in in previous versions of Excel.

In the first post here, I showed you how to transform a simple table into a PivotTable. In the next post here we moved one step further to transform a class schedule into a PivotTable. The complexity in that example was rooted in the fact that the range had merged cells with multiline data.

In today’s post we will move one step further, and transform a nested table into a PivotTable. The following screenshot shows you the format we will start with. We have a nested table with fake sales data by Country, Product, Year and Month. This is quite a common format. When you stumble upon such a table, you wish the author would use a PivotTable instead. Continue reading “Transform a nested table to PivotTable”

Back to School – Transform class schedule to PivotTable

(By: Gil Raviv. Originally published here).

This is the second post in the series The Definitive Guide to Unpivot with Power Query in Excel. For today’s post I picked a topic which is quite actual for many of us. Our kids were recently back to school. By now their weekly schedule is probably stable, and we can use Excel to perform some data analytics on the schedule. Which teacher is the busiest? Does our kid learn enough Math vs. English? (If this topic sounds too simplistic for data analytics, don’t worry, you will see some non-trivial and very useful transformations in this post).

Continue reading “Back to School – Transform class schedule to PivotTable”

Transform already-pivoted tables to PivotTable (The Definitive Guide to Unpivot with Power Query in Excel)

(By: Gil Raviv. Originally published here).

This post is the first in a series of posts that will walk you through one of the coolest data transformation features in Excel. If you are not familiar with Power Query Add-in for Excel, or never had the time or energy to start learning it, this series of posts will convince you to start.

But let’s start with the big and common challenge. As an advanced Excel user, you prefer to analyze or create your reports with PivotTables and PivotCharts. Unfortunately, many of your colleagues are not so familiar with it, and prefer to share with you their static tabular data, or summaries. You get their static tables, but cannot convert it into PivotTable and shape it to meet your analysis needs.

Can you transform a static pivoted table into a PivotTable? 

Another common scenario – You have an Excel workbook that is used by your team to report project progress.

Continue reading “Transform already-pivoted tables to PivotTable (The Definitive Guide to Unpivot with Power Query in Excel)”

Create a free website or blog at WordPress.com.

Up ↑