The Ken Puls (Excelguru) Blog
807 FOLLOWERS
Excelguru is a blog dedicated to helping you get the most out of Microsoft Excel.
The Ken Puls (Excelguru) Blog
1M ago
Now I know that not everyone is going to want to do this, but one of the things that I find which really helps me understand complex calculations or visuals, is to manually replicate the values used. It helps me understand the methods, exact values plotted and allows me to really validate the output. So just for fun, let’s build a quick Box and Whisker chart, then prove out the values that have been plotted.
Creating the Box and Whisker chart
For this post, we are going to create a simple Box and Whisker chart using the defaults that Microsoft provides. (Keep in mind that you can, of course, c ..read more
The Ken Puls (Excelguru) Blog
1M ago
Introduction
I’m a big fan of building Box and Whisker plots (also known as Box plots to those in the statistics world) to interpret certain data sets. While I tend to use these for charting Power Query refresh times, they really can be used for anything where you are trying to understand the variation of your results. The only criteria is that the values should be numeric like daily sales, number of warranty returns, air quality indices, or temperatures for example. These charts offer a lot of information – if you know how to interpret them. (And if you don’t… have a read of this article whic ..read more
The Ken Puls (Excelguru) Blog
1M ago
Introduction
I’m a big fan of Excel's Box and Whisker chart (also known as Box plots to those in the statistics world). While they can be used in a variety of applications, my personal use case is to try and understand the performance of Power Query refresh times – something that my Monkey Tools add-in can do for you. Unfortunately, a lot of people don’t really understand the chart, so the purpose of this article is to dive in, explain how they work, and what conclusions you can draw from the data.
What do the Boxes, Whiskers and Dots All Mean?
The image below provides a quick summary of what ..read more
The Ken Puls (Excelguru) Blog
2M ago
The question of how to convert Julian dates to Gregorian dates using Power Query came up today in a course I was teaching. Although I haven't ever used JD Edwards myself, apparently this is quite common in that system, so it hits a lot of people. In this article, we will look at the method to do so.
What the Heck is a Julian Date Anyway?
The "standard" calendar around the world in most cultures today is the Gregorian calendar, which actually replaced the Julian calendar due to the latter's "drift" on leap years. To keep it short, the Julian calendar is an antiquated calendar that doesn't handl ..read more
The Ken Puls (Excelguru) Blog
2M ago
After writing up my article on connecting to Spotify using Power Query, I thought it would be helpful to provide some functions that will allow you to get album and track data from Spotify.
A sample project to illustrate getting album and track data from Spotify
Rather than just dump a bunch of functions on you, let's walk through a project to get all albums and songs released by an artist, as well as Spotify's song popularity and other statistics. Now you can choose any artist you want for this, but I'm going to use Taylor Swift, given her extensive catalogue (which requires dealing with pagi ..read more
The Ken Puls (Excelguru) Blog
2M ago
Spotify has a massive music database, and in this article we're going to look at how to connect to Spotify using Power Query.
Step 1: Get API credentials from Spotify
The first thing you'll need to do is to collect a "Client ID" and "Client Secret" from Spotify's developer portal. Of course, you'll need a Spotify account for this, but the good news is that you can sign up for a free account if you don't already have one.
To generate (or access) your Client Key and Client Secret, the steps are as follows:
Log in to the Spotify Developer portal
Create a new Application
Expand the "Show Client S ..read more
The Ken Puls (Excelguru) Blog
2M ago
COACHED TRAINING: Excel Fundamentals Boot Camp
Course Description
In the Fundamentals Boot Camp, you will begin with a review core skills for the Excel analyst. This section is geared to teach critical skills to less experienced users and review those skills for intermediate users. We’ll start with basic formulas, reviewing absolute and relative referencing, and move into a review of Math, Logic, Text and Date functions. From there, we’ll look at how data SHOULD be structured in Excel for advanced analytics, then dive deep into Excel’s most feared function: VLOOKUP. (Yes, we are very aware th ..read more
The Ken Puls (Excelguru) Blog
2M ago
You read that correctly, the Data Monkey has landed, and Master Your Data for Excel and Power BI is now available in PDF format from your favourite online bookstore!
You may have noticed that the past few months have been pretty quiet on the Excelguru blog. A big reason for this is that I essentially went offline to focus on finishing this book. As many of you know, it’s been pushed back before (more than once), and we wanted to ensure that this would not happen again. I’m pleased to say that the final version has now gone to the printers, and we’ll see physical copies start ..read more
The Ken Puls (Excelguru) Blog
2M ago
For a while now, I have been wanting to have the capability in Power Query to select a query by name PROGRAMMATICALLY. Why? Building a SelectQuery Function would allow me to execute one of multiple “Transform” queries depending on a user selection on the Excel sheet. This will help me process log files from multiple vendors which each have different contents and field names.
Here is a fairly simple example with only three input queries (although my true setup actually has seven potential queries to select from:
How the setup is intended to work:
I have a named range called “User_S ..read more
The Ken Puls (Excelguru) Blog
2M ago
This past weekend we published a major redesign at Skillwave.Training. Months in the making, this has been a total overhaul to focus on delivering the best online learning experience for our clients. Check out some of the images from the new site:
Centralized Dashboard
When you log in, you’ll be taken to your Dashboard immediately. This is the one stop console that will let you access any of your active course subscriptions, review forum posts, download your files, and manage your billing and profile details. We’ve worked hard to make this dashboard intuitive and easy ..read more