All articles with 'power query' Tag

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.

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.

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.

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.

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?

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.

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.

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.

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.

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. 

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.

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.

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.

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.

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 ?

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