Search

DataChant

Chants of big data & bigger insights

Month

January 2016

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)”

Excel for Security Analysts – Episode 2 – Import Multiline Logs

(By: Gil Raviv. Originally published here).

In today’s episode on Excel for Security Analysts, we will import SSH logs to Excel. To read more episodes of Excel for Security Analysts, go here.

The idea for this post came from a TechNet forum thread that started by a Data Analyst here (Thank you Rajender for sharing the challenge and the logs).

You start with a log file like this:

And you should end up with a table in Excel:

Continue reading “Excel for Security Analysts – Episode 2 – Import Multiline Logs”

Transform multiline records into a table

This blog post was originally posted on TechNet and is one of the most viewed posts I have written 🙂

In today’s post, there is a nice Power Query trick to transform multiline record data into a table in Excel. I intend to use this trick quite soon on my next two posts as part of the Excel For Security Analysts series (So stay tuned for some magic on SSH and IPTables firewall logs).

We have many cases where our raw data is formatted in multiline attribute-value pairs. The first column contains the attributes, and the second column contains the values. Analyzing the data with such format is quite challenging.

Continue reading “Transform multiline records into a table”

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 ↑