All articles with 'power query' Tag

Introduction to Power BI – What is it, how to get it, how to create reports with Power BI and how to publish them?

Published on Nov 8, 2019 in Power BI
Introduction to Power BI – What is it, how to get it, how to create reports with Power BI and how to publish them?

Power BI is a data analytics & visualization software. It is one of the most popular and powerful way to work with complex business data. In this page, you will find a comprehensive guide to start your Power BI journey.

Continue »

How can you analyze 1mn+ rows data – Excel Interview Question – 02

How can you analyze 1mn+ rows data – Excel Interview Question – 02

As part of our Excel Interview Questions series, today let’s look at another interesting challenge. How can you analyze more than 1 million rows data in Excel?

You may know that Excel has a physical limit of 1 million rows (well, its 1,048,576 rows). But that doesn’t mean you can’t analyze more than a million rows in Excel.

The trick is to use Data Model.

Continue »

Yummy WAFFLE chart in Power BI

Published on Sep 3, 2019 in Power BI, Power Query
Yummy WAFFLE chart in Power BI

Waffles are yummy, Power BI is awesome. The combination is going to send your taste-buds on a cruise. Learn how to make a yummy waffle chart in Power BI.

Continue »

Job Title Matching Problem [Excel Homework]

Published on Aug 30, 2019 in Excel Challenges, Power Query
Job Title Matching Problem [Excel Homework]

Howdy folks. Almost the end of August here. Let’s wrap it up with a nice little challenge, inspired from my recent consulting gig. Say you are looking at few job titles that look similar and want to match them to correct title.

Continue »

How to extract common values in two tables? – Power Query Tip

Published on Aug 19, 2019 in Power Query
How to extract common values in two tables? – Power Query Tip

We, humans like to compare. Whether we are on Facebook or workbook, we want to compare. So how do you compare two tables and extract common values? Simple, use Excel Power Query. It can merge (a la join) tables and give you the common values.

Continue »

Combine multiple Excel files using Power Query [Full example + download]

Published on May 30, 2019 in Power Query
Combine multiple Excel files using Power Query [Full example + download]

Say you want to combine multiple Excel files, but there is a twist. Each file has few tabs (worksheets) and you want to combine like for like, ie , all Sheet1s to one dataset, all Sheet2s to another dataset…

To make matters interesting each sheet has a different format.

What now?

Of course Power Query to the rescue.

Continue »

How many calls we got outside office hours? [Excel / Power Query homework]

Published on May 3, 2019 in Excel Challenges, Power Query
How many calls we got outside office hours? [Excel / Power Query homework]

Time for another Excel formula / Power Query challenge. This is based on a common business data analysis problem. Say you have two tables – calls log and office hours. Call log tells when each call is received. Office hours tell us working hours for seven days of the week. We want to know how many calls are outside office hours.

Continue »

Employee Turnover Dashboard – Power BI for HR

Published on Mar 8, 2019 in Power BI, Power Pivot, Power Query
Employee Turnover Dashboard – Power BI for HR

Work in HR and use Power BI? You are going to love this extensive, powerful and useful Employee Turnover Dashboard. In this detailed article, learn how to create your own attrition dashboard system with Power BI. Full example workbook, video and sample data included.

Continue »

Quick tip: Make a list of numbers (or dates) in Power Query easily

Published on Nov 13, 2018 in Power Query
Quick tip: Make a list of numbers (or dates) in Power Query easily

Just a quick tip to revive the blog from a month long silence. I am alive and kicking. I have been occupied with a quest to rescue princess & maidens on video game console. Recently we bought SNES classic console from Nintendo and I have been playing Legend of Zelda – a link to past regularly. As it is almost summer, I am also enjoying the beautiful outdoors in Wellington. All this means, little time for blogging. I will try to post a few more times before the end of year.

Make a list of numbers in a jiffy with Power Query:

We know that in Excel, you can type a few numbers and use the fill handle to fill down (or up etc.) numbers as you want.

But what if you need some numbers in Power Query?

Continue »

Leave entitlement vs. usage analysis with Power Query

Published on Sep 24, 2018 in Excel Challenges, Power Query
Leave entitlement vs. usage analysis with Power Query

Last Friday, I asked you to analyze “sick leave entitlement vs. usage” data and answer homework questions. We got several interesting responses to that. Today, let me share a quick video highlighting how to analyze such data with Power Query.

This is part of our Power Mondays series, where every Monday you will learn something new & useful about Power BI, Power Query and Power Pivot.

Continue »

How many people used their entire sick leave entitlement? [Power Query / Excel homework]

Published on Sep 21, 2018 in Excel Challenges
How many people used their entire sick leave entitlement? [Power Query / Excel homework]

Imagine you are the HR analyst at BigLargeCompany. You are asked to find out whether staff at BLC (BigLargeCompany you silly) use up their full sick leave entitlement.

You have two tables – emps & leaves as illustrated below.

sample data - sick leave entitlement vs. usage

Your mission is to find out answers to below questions.

  • How many employees used exactly 100% of their entitled sick leave?
  • How many employees did not take any sick leaves?
  • Listing of all employees who used 100% of their entitlement

 

Use either Power Query, Excel formulas or any other technique to answer the questions.

Continue »

5 tips: Power Query for Accountants (and finance people) – Free Masterclass

Published on Sep 3, 2018 in Master Class, Power Query
5 tips: Power Query for Accountants (and finance people) – Free Masterclass

Do you work with accounting or finance data? You are going to love, no scratch that, adore Power Query. It can save you precious time, make you look like a hero in-front of clients and keep you sane.

In this masterclass, learn all about Power Query for accountants (and other kinds of finance people) & 5 tips.

Why Power Query?

Power Query is a data processing software. Using Power Query, you can create process driven programs (queries) to collect data, clean or reshape it, calculate things and publish final output as a table. Once you create a query in PQ, when the underlying data changes, you just refresh the query and brand new data will be fetched, cleaned and published for you.

For example, you can use Power Query to combine ledger files from various accounts to one master file with Power Query. No more VBA or manual copy pasting. Just one query and you are done.

Continue »

What is Power BI, Power Query and Power Pivot?

Published on Aug 27, 2018 in Power BI, Power Pivot, Power Query
What is Power BI, Power Query and Power Pivot?

In this installment of Power Mondays, let’s go the very basics of it all.

What is Power BI?

Power BI is a software to create and publish interactive, web-enabled reports & visualizations for your audience. You can use Power BI on a PC or web to create things. Once you publish a report (or few visuals), your audience can consume them by using any device – PCs, Macs, Web browsers, Apps on cell phone / tablets etc. 

Continue »

How to get a random sample of data with Power Query

Published on Aug 20, 2018 in Power Query
How to get a random sample of data with Power Query

Today’s Power Monday trick is about Power Query. This is based on my experience of working with large volumes of data.

Today I have been building a hotel dashboard (more on this later). As part of the dashboard, I wanted to show a random sample of user reviews. Reviews database had quite a few rows, so I wanted to extract a randomized sample of 100 reviews and show them in the report. When you refresh the report (Data > Refresh), then a new set of reviews will be fetched and shown.

Let’s learn how to generate a random sample with Power Query in this article.

Continue »

Nest Egg Calculator using Power BI

Published on Aug 6, 2018 in Power BI, Power Pivot, Power Query
Nest Egg Calculator using Power BI

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 »