Proving Excel’s Box and Whisker Chart Calculations
The Ken Puls (Excelguru) Blog
by Ken Puls
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
Visit website
Building a Box and Whisker Plot in Excel
The Ken Puls (Excelguru) Blog
by Ken Puls
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
Visit website
Reading Excel’s Box and Whisker chart
The Ken Puls (Excelguru) Blog
by Ken Puls
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
Visit website
Convert Julian Dates to Gregorian Dates with Power Query
The Ken Puls (Excelguru) Blog
by Ken Puls
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
Visit website
Get Album and Track Data from Spotify
The Ken Puls (Excelguru) Blog
by Ken Puls
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
Visit website
Connect to Spotify using Power Query
The Ken Puls (Excelguru) Blog
by Ken Puls
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
Visit website
Excel Fundamentals Boot Camp
The Ken Puls (Excelguru) Blog
by Ken Puls
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
Visit website
Master Your Data is Now Available!
The Ken Puls (Excelguru) Blog
by Ken Puls
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
Visit website
Building a SelectQuery Function
The Ken Puls (Excelguru) Blog
by Alex Jankowski
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
Visit website
Major redesign at Skillwave.Training
The Ken Puls (Excelguru) Blog
by Ken Puls
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
Visit website

Follow The Ken Puls (Excelguru) Blog on FeedSpot

Continue with Google
Continue with Apple
OR