Search

DataChant

Chants of big data & bigger insights

Tag

Power Pivot

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

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

 

My Calendar Insights in Excel 2016

This week we have announced on Office Blogs the release of 3 new Excel templates that harness advanced data analytics technologies in Excel 2016:

  • Power Query (Excel 2016 new get-data and transformation features that are available in the Get & Transform section of the Data tab).
  • Power Pivot measures.

You are welcome to try out the new templates and examine their queries and DAX formulas. The templates are available in File –> New section of Excel 2016.

IMG_0725

If you can’t find the template in File –> New, you can download it from TechNet Gallery here.

Continue reading “My Calendar Insights in Excel 2016”

Blog at WordPress.com.

Up ↑