Search

DataChant

Chants of big data & bigger insights

Tag

PivotTable

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”

Advertisements

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”

Excel for Security Analysts – Intro

(Originally published here)
The demo I will share with you today is something that I was itching to try for a while.
Before I joined Microsoft and Excel team, I had spent 14 years in developing Network & cyber Security products. Whether we had been developing secure web gateway, innovating in the realm of Security Information & Event Management (SIEM), or battling cybercrime with Big Data & Machine Learning, one thing was always in common ‚Äď You start with logs. Lots of logs, and sometimes you end up with Excel.

Detecting cybercrime and advance threats is not an easy task, but the adversaries will eventually leave their traces, and these traces can be found in logs. There are plenty of great products and technologies out there that help Security Analysts to defend their organizations. But here is an over-simplification of all these advanced detection technology:

Excel is mainly used in these solutions to start a forensic investigation with small portion of the log data, or prepare customized reports to the chief security officer.
But Excel can do so much more, and here is an over-simplification of it:
With the help of Power Query and Power Pivot you can import millions of rows, shape the data, and load it into the Data Model for PivotTable and PivotChart analytics.
In this series of blog posts dedicated for Security Analysts¬†‚Äď we will unleash the power of Excel to battle cybercrime and save the day for the Security analyst. For those of you who are BI professionals, and know all the tricks, get ready to make new friends in your¬†Information Security team.
Episode 1: From web traffic logs to powerful dashboard
In today’s post we will show you how to turn web traffic logs (W3C Extended Log File Format) into a powerful Excel workbook.
Web traffic logs are generated by web proxies and application firewalls to track inbound and outbound traffic. Each line in the log represents a web request and its response along with their attributes.
We will use a ~60MB log sample here that was shared by Dr. Anton Chuvakin for learning purposes.
Using Excel 2016 Preview, we will import the file, shape it with Power Query, load it to the Data Model and build powerful analytics tools with PivotTables and PivotCharts.

Start the walkthrough

 

Create a free website or blog at WordPress.com.

Up ↑