Search

DataChant

Chants of big data & bigger insights

Tag

Excel 2016

Faster Matrix Mulitplication

As a followup for our recent Matrix Multiplication post here, I would like to share with you a faster query for Matrix Multiplication (Faster only relative to the previous ones I shared. Excel is probably not the best tool for big matrices, and you may still need to use other tools such as R).

I would like to thank Imke Feldmann and Bill Szysz for sharing their queries. It turns out that the use of List.Generate is quite slow in comparison to the native Power Query transformations that we will share today.

Note: If you are an advanced Power Query practitioner, you can skip to the end of this blog to see the final M expression, and download the workbook sample.

Continue reading “Faster Matrix Mulitplication”

Advertisements

Analyze Linkedin Company Page with Excel and Power BI

Your company’s page on Linkedin is a treasure trove of insightful data. In today’s post I will show you how to import this data to Excel or Power BI Desktop.

In my posts here and here, we focused on the companies-search Linkedin API call, which allowed us to extract companies by specific specialties/domains and perform analysis of industry, geolocation and correlation between different specialties.

Today we will focus on your company page on Linkedin, and learn how to extract your company’s status updates, their likes and comments, and the persons who were engaged with your page.

Continue reading “Analyze Linkedin Company Page with Excel and Power BI”

Watch our webcast at PASS Excel BI Virtual Chapter

Hi everyone,

If you missed our post here, or want to refresh your skills on Excel, Power Query and analysis of Facebook Page Insights, check out our recorded webcast at PASS Excel BI Virtual Chapter, which walk you through the creation of an Excel report that measures the engagement level of Facebook Pages.

In the demo we compare all US Election Candidates by their storytellers on Facebook.

Enjoy the movie 🙂

Analyze US Election Candidates in Excel and #PowerQuery

As promised here, today we will build a dashboard that analyzes Facebook Insights on US election candidates.

As a teaser, here is a screenshot of a 3D Map you will be able to create when we are done:

3dmap

Continue reading “Analyze US Election Candidates in Excel and #PowerQuery”

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”

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 ↑