All articles in 'Power Query' Category

Nest Egg Calculator using Power BI

Published on Aug 6, 2018 in Power BI, Power Pivot, Power Query

Welcome to Power Mondays. Every Monday, learn all about Power BI, Power Query & Power Pivot in full length examples, videos or tips. In the first installment, let’s take a look at something we all can related to – Money. 

We all know that Power BI is good for creating awesome visual experiences. Today let me share another fun way to use Power BI – to build a calculator. Learn how to create nest egg calculator in this Power BI parameter example tutorial.

Continue »

Top 5 HR Analytics Examples – Free Video Masterclass

Published on Jul 27, 2018 in Learn Excel, Master Class, Power Query
Top 5 HR Analytics Examples – Free Video Masterclass

I recently finished a long consulting gig with one of the government ministries in New Zealand. Guess what I was doing? HR Analytics and Reporting. In this post, I want to share my top 5 Excel tips for HR people, based on what I learned in the last 18 months.

Specifically, we will cover:

  • Gathering and structuring Employee data in Excel
    • How to use Power Query to collect data
    • Polish / clean data in Power Query
    • Bring cleaner data to Excel as refreshable table
  • Answering questions about employees
    • Using Excel formulas such as COUNTIFS, SUMIFS, AVERAGEIFS
    • Pivot tables for data analysis
    • Understanding the results quickly with conditional formatting
  • Understanding pay gap
    • Calculating gender pay gap
    • Visualize pay gap
  • Creating salary distribution charts
    • Working with histogram charts in Excel 2016 / Office 365
    • Making interactive charts
  • Generating letters thru mail merge
    • Calculating employee bonus based on bonus mapping logic
    • Creating 100s of letters with a single click using Mail Merge + Word

Sounds interesting? Read on for details.

Continue »

Mutual Fund Portfolio Tracker using MS Excel

Published on Jul 6, 2018 in Learn Excel, personal finance, Power Query, technology
Mutual Fund Portfolio Tracker using MS Excel

Would you like to spend next 5 minutes learning how to create an mutual fund tracker excel sheet?

Make a live, updatable mutual fund portfolio tracker for Indian markets to keep track of your investments using this example.

Continue »

How to undo in Power Query [Quick Tip]

Published on Jun 22, 2018 in Power Query
How to undo in Power Query [Quick Tip]

Ever wondered how to undo in Power Query. If you try to press CTRL+Z or look for undo icon in Power Query (either in Excel or Power BI), you will not find it. The reason is simple. There is no undo in Power Query. So how to undo ?

Continue »

Best Excel Books & Power BI Books – 2018

Published on Jun 11, 2018 in Learn Excel, Power BI, Power Pivot, Power Query
Best Excel Books & Power BI Books – 2018

So you have decided to up your game with Excel and / or Power BI this year and now ravenously looking for books to read. You have come to the right place. Here is my list of recommended best Excel books, and books on Power BI, visualization, dashboards, VBA, Macros and analytics.

Use below links to navigate the relevant section of this page:

Continue »

FIFA Worldcup 2018 Excel Tracker – FREE Download

Published on Jun 7, 2018 in Power Query, Templates
FIFA Worldcup 2018 Excel Tracker – FREE Download

FIFA world cup 2018 is around the corner. I love soccer, I love Excel, Let’s marry them. Here is an awesome, free FIFA world cup Excel Tracker to help you follow this year’s games in Russia.

Click here to download the FIFA worldcup 2018 tracker.

What you can do with this FIFA world cup Tracker Excel?

You can use this tracker to,

  • View schedules in your local time for group and knockout stages
  • View summary and detailed points table
  • Refresh live points table. When you refresh, the tracker show updated points based on latest results (You need Excel 2016, Office 365 or older versions of Excel with Power Query)
  • View knockout stage matches as a bracket
  • See timeline of the matches
Continue »

A trick to Pivot text values

Published on Apr 30, 2018 in Pivot Tables & Charts, Power Query
A trick to Pivot text values

We all know that Pivot Tables are best thing since avocado on toast. But they can’t slice text values and spread them in a table with Pivots. So how to take a large blob of text and turn it in to something meaningful like above?

Simple, we use Power Query.

Continue »

How your country did in Commonwealth Games – Power BI Viz and Tutorial

Published on Apr 17, 2018 in Power BI, Power Pivot, Power Query
How your country did in Commonwealth Games – Power BI Viz and Tutorial

Commonwealth games 2018 have ended in the weekend. Let’s take a look at the games data thru Power BI to understand how various countries performed.

Here is my viz online or you can see a snapshot above.

Looks good, isn’t it? Well, read on to know how it is put together.

Continue »

Visualizing Commonwealth games performance – Interactive chart

Published on Apr 13, 2018 in Charts and Graphs, Power Query
Visualizing Commonwealth games performance – Interactive chart

The 2018 edition of Commonwealth games are on for a week now. Both of my homes – India and New Zealand have been doing so well. Naturally, I wanted to gather games data and make something fun and creative from it. Here is my attempt to amuse you on this Friday.

Looks interesting? Want to know how to make something like this on your own? Then read on…

Continue »

Rescue oddly shaped data – Battle between Formulas, VBA and Power Query

Published on Apr 11, 2018 in Learn Excel, Power Query, VBA Macros
Rescue oddly shaped data – Battle between Formulas, VBA and Power Query

Let’s say you have data like this in a spreadsheet. Don’t roll your eyes, I am 102% sure, right at this moment, someone is (ab)using Excel to create similar messy data.

How do you reshape it to one column?

You could use formulas, VBA or Power Query. Let’s examine all these methods to see what is best. All these methods assume your data is in a range aptly named myrange.

Continue »

How windy is Wellington? – Using Power Query to gather wind data from web

Published on Feb 22, 2018 in Power BI, Power Query
How windy is Wellington? – Using Power Query to gather wind data from web

Let’s take a whirlwind trip to coolest little capital – Wellington. It is a windy place, so hold on to your hats and spreadsheets.

Almost everyone who spends more than 2 days in Wellington would agree that it is a windy place. But how windy is Welly? In this two part series, we will use Power Query, Excel charts and coffee to answer that question.

But, first let’s start with a joke.

What happens when you throw a boomerang in Frank Kitts Park?

You will have to buy another one, coz you are not getting that one back.

Continue »

D’oh – Visualizing Homer’s favorite sayings in Power BI

Published on Sep 29, 2017 in Power BI, Power Pivot, Power Query
D’oh – Visualizing Homer’s favorite sayings in Power BI

Before we begin:

Today is the last day for enrolling in our Power BI Play Date. Don’t miss out on this amazing opportunity to learn, use and benefit from Power BI at your work. Check out my online class and sign up before the doors close at midnight. Click here.

Let’s get our Simpsons on then.

D’oh, How often Homer says his favorite things?

Here is the visualization to explore Homer’s (and other character’s) favorite sayings in 27 years worth of Simpsons episode. Click on the image to play.

Continue »

Convert unevenly spaced list to table [Data from Hell]

Published on Aug 30, 2017 in Power Query
Convert unevenly spaced list to table [Data from Hell]

Introducing Data from Hell:

Watch out, its data from hell. In this new video series, we are going to examine some nutty, frustrating and fun data reshaping challenges and solve them using Excel. We will use Power Query, Formulas, VBA or other features as needed to free this data from damnation.

For our first installment, let’s reshape unevenly spaced list of values to a table.

Continue »

Employee Performance Panel Charts in Power BI with R

Published on Aug 11, 2017 in Power BI, Power Query
Employee Performance Panel Charts in Power BI with R

Yesterday we saw a beautiful example of panel charts with R. Today let me show you how to create the same (or even better) with Power BI & R. What you need: Power BI Desktop and R Raw data set – rem-data.csv Creating Panel Charts in Power BI with R Load CSV data in to […]

Continue »

Extract currency amounts from text – Power Query Tutorial

Published on Jul 27, 2017 in Power Query
Extract currency amounts from text – Power Query Tutorial

Let’s say you got some text values and want to extract the amounts from them. Something like above.

How to go about it?

We could use a variety of techniques to extract the values.

Continue »