All articles with 'power query' Tag

Figuring out Employee Churn with Power Query [HR Analytics]

Published on Mar 13, 2017 in Power Query
Figuring out Employee Churn with Power Query [HR Analytics]

Let’s say you are the people manager at ACME Inc. You are looking staff list for the months – January and February 2017. You see that we had 4,000 employees in Jan and 4,200 employees in Feb. So what is the churn?

  • Is it just 200?
  • Or is it the sum of people who left and who joined?
  • What if you want to find out how many people moved to new designations / departments or groups?

You see, churn is tricky to figure out.

So why not invite the pros? ie Power Query. 

Continue »

Visualizing Financial Metrics – 30 Alternatives

Published on Aug 30, 2016 in Analytics, Charts and Graphs
Visualizing Financial Metrics – 30 Alternatives

Around 2 months back, I asked you to visualize multiple variable data for 4 companies using Excel.  30 of you responded to the challenge with several interesting and awesome charts, dashboards and reports to visualize the financial metric data. Today, let’s take a look at the contest entries and learn from them.

First a quick note:

I am really sorry for the delay in compiling the results for this contest. Originally I planned to announce them during last week of July. But my move to New Zealand disrupted the workflow. I know the contestants have poured in a lot of time & effort in creating these fabulous workbook and it is unfair on my part. I am sorry and I will manage future contests better.

Continue »

Unpivot and then pivot for clarity (case study)

Published on Mar 8, 2016 in Pivot Tables & Charts, Power Query
Unpivot and then pivot for clarity (case study)

Or more appropriately titled, the one where Power Query solves the problem in less time than it takes you to say Get & Transform Data.

Recently, one of my students Mr. K, sent me a pivot table problem.

Today my boss asked me “how much we paid to staff since the inception of our business with their respective date of joining?” He wanted to know, level wise summary of the last 16 years (on Quarterly / Year wise basis).

The records appended from the database month wise. Have a look to the file and give your ideas.

Mr. K’s data looked like above.

Continue »

CP052: Book Review – M is for Data Monkey by Ken & Miguel

Published on Feb 11, 2016 in Chandoo.org Podcast Sessions, Power Query
CP052: Book Review – M is for Data Monkey by Ken & Miguel

In the 52nd session of Chandoo.org podcast, let’s discuss monkeys, Ok, I am kidding. We are going to talk about M is for Data Monkey book.

What is in this session?

In this podcast,

  • Updates: Why so much gap between episodes?
  • Quick introduction to Power Query
  • Why you should get this book?
  • What is in this book?
  • A very cool example of the techniques you will learn
  • Conclusions
Continue »

Transpose this address data [VBA homework]

Published on Jan 16, 2016 in Excel Challenges, Power Query, VBA Macros
Transpose this address data [VBA homework]

Here is an interesting problem to keep you busy.

Transpose the address data in column A into the format indicated in C:G using either VBA, formulas or Power Query. Once done, post your answers in comments section.

Continue »

Unpivot data quickly with Power Query [tutorial]

Published on Sep 29, 2015 in Pivot Tables & Charts, Power Query
Unpivot data quickly with Power Query [tutorial]

Power Query (Get & Transform data in Excel 2016) is a must have tool, if you wrangle with data every day. Here is a quick introduction, in case you are new.

Let’s learn how to use Power Query to unpivot data.

Essentially, we are trying to go from left to right in the above picture.

Doing something like this thru either formulas or VBA can be very complex. But Power Query can get you unpivoted data in just a few clicks. Sounds interesting? Read on.

Continue »

How to import web data to Excel using Power Query

Published on Aug 21, 2015 in Power Query
How to import web data to Excel using Power Query

Power Query offers many ways to get data to Excel. One of them is to Web Data import feature. Let’s understand how this works by importing world stock exchange closing data from Google Finance website.

[Related: Introduction to Power Query]

Continue »

CP040: Intro. to Power Query – What is it and how to get started – with Miguel Escobar

Published on Jul 30, 2015 in Chandoo.org Podcast Sessions, Power Query
CP040: Intro. to Power Query – What is it and how to get started – with Miguel Escobar

In the 40th session of Chandoo.org podcast, Let’s talk about Power Query. I have the pleasure and fortune to catch up with Miguel Escobar (who along with Ken Puls runs PowerQuery.Training website) and talk about this very exciting piece of technology and how it can make our life simpler.

What is in this session?

In this podcast,

  • Welcome
  • Miguel’s introduction, background and current projects
  • What is Power Query
  • How to install it
  • Sample use cases of Power Query
  • What is Power BI
  • Resources for learning Power Query – Books & Courses
Continue »

How to consolidate data that is different shapes [BYOD]

Published on Feb 16, 2015 in Excel Howtos, Pivot Tables & Charts, VBA Macros
How to consolidate data that is different shapes [BYOD]

Last week, I asked my email newsletter readers to submit “one data analysis problem you are struggling with”. We called it BYOD – Bring your own data. More than 100 people have emailed various interesting (and often very difficult) problems. This week (between 16th of February to 20th of February), let’s take a look at some of these problems and solve them.

Consolidating data in different shapes

We can use either VBA or Excel’s consolidation features to combine data that has same shape (ie same number & type of columns). Here is one way to do it.

But what if we need to consolidate data that is in different shapes?

Something like above.

In such cases, we can use 3 powerful tools.

  1. Multiple Consolidation Ranges – Pivot Tables
  2. VBA
  3. Power Query

So let’s examine how to use these approaches to consolidate data in different shapes.

Continue »

A dashboard to visualize this FIFA worldcup [guest post]

This is a guest post by Krishna, a football lover & one of our readers.

The wait for lifting the most valued priced in football for Germans was finally over. For a football fan, world cup is best time that is scheduled every four years and that if your favorite team lifting the trophy is like your crush is going on a date with you. 🙂

A sneak-peek at the final dashboard

Here is the final dashboard (it has more functionality than depicted). Click on it to enlarge.

FIFA 2014 Worldcup Excel dashboard

Continue »

CP015: Handling big data, Controlling model railroad sets, Overcoming Excel obsession & more – ASK CHANDOO

Published on Jul 24, 2014 in Chandoo.org Podcast Sessions

In the 15th session of Chandoo.org podcast, lets answer some of your burning Excel questions.

Handling big data, Controlling model railroad sets, Overcoming Excel obsession & More - ASK CHANDOO

What is in this session?

Around last week, I invited you to ask me anything. More than 150 people responded to this call and sent in their questions. Since answering all the questions is not possible, I handpicked roughly 10 questions to answer in this episode of Chandoo.org podcast.

In this podcast, you will learn,

  1. How to fill blank cells with data from above
  2. How to work with Big data in Excel
  3. How to combine data from multiple sources & analyze it in Excel
  4. How I am managing my life after starting Chandoo.org
  5. How to create and distribute stand-alone Excel products
  6. How to control a model railroad set using Excel VBA (not fully answered)
  7. & more…
Continue »

CP003: Business Intelligence for Masses – Interview with Mike Alexander

Published on Mar 27, 2014 in Chandoo.org Podcast Sessions
CP003: Business Intelligence for Masses – Interview with Mike Alexander

In the third session of Chandoo.org podcast, we are going to get BI curious. 😉

Not that kind you silly, We are talking about Business Intelligence, Big Data, Power Pivot & other Power BI family members. In this session, I am happy to feature Mike Alexander – Microsoft MVP, Author, Blogger & a good friend. Mike talks about how Excel is shaping the BI (Business Intelligence) revolution with advent of Power BI functionality.

You will learn,

  • Introduction, what Mike is up to these days?
  • What is BI, what does it mean to an average Excel analyst?
  • What BI capabilities Excel has – brief intro to each of them
    • Power Pivot & what it does
    • Power Query & why it is important
    • Power View & how it works (and where it sucks)
    • Power Maps
  • How to learn about these new technologies
    • Recommended Books
    • Websites
    • Courses
    • Live classes
  • Special gift for our listeners
Continue »