Articles
Looking for something specific? Search here for relevant content.
Most Recent Posts
I've been using Microsoft Office for a long time - long before there were web versions of Excel, Word and Power Point. And while Microsoft has done a great job...
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,...
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...
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...
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...
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...
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...
Recently, a reader commented on a blog post that I wrote back in 2015. Their question essentially boiled down to working out how to label duplicates with Power Query. As...
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...
Master Your Data with Excel and Power BI If you are struggling to clean up your data in Excel or Power BI, or spending time performing repetitive data cleanup on...
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...
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...
We're super excited to let you know that we've just released some new Monkey Tools features! Let's take a quick look as to what is new... The Table Monkey This...
Wow, it is hard to believe it is already December. And looking back at my blog, I realized that I forgot to tell you that we released a few more...
You know the drill… extract, transform and load your data, relate your tables, then create basic DAX measures. All work that needs to be done before you can really get...
We are rapidly approaching November 1, 2020, which has been the latest release target for our Master Your Data book. This is the long-awaited second edition of M is for...
A question came up in the Excelguru forums today about how to use Excel tables to filter a Power Query. While Power Query can't read a filter from an Excel...
We've been kind of quiet here, but we're excited to announce that we've just published an update to Monkey Tools QuerySleuth feature. It now contains an "tabbed" experience so that...
We’re super excited to announce that we have given the Monkey Tools calendar creator feature an upgrade. In fact, it is so much of a power up, that this feature...
Earlier this week, in a private forum, one of my colleagues took a friendly joke thread and dropped a horrible MeToo joke in it. I was astounded, incredulous and disgusted....
Most Commented Upon Posts
One of the things that I’ve complained about in the past is that there is no built-in way for Power Query to be able to pull up the path for...
I got sent this today from a friend. He was a bit frustrated, as he got a message from Power Query that read “Formula.Firewall: Query 'QueryName' (step 'StepName') references other...
When I’ve finished building a solution in Excel, I like to give it a little polish, and really make it easy for my users to update it. The last thing...
At our last MVP summit, I sat in on a session on PowerQuery. Our presenter, who is pretty passionate about it, at one point asked us “Who has wanted to...
I got a comment on a previous post today, which made me realize I’d promised this but never posted it. So let’s look at how to combine multiple workbooks together...
If you're working with the Office 2007 Ribbon, you may have come to the conclusion that there are areas of, the RibbonX object model which are kind of weak. One...
Tonight I decided to actually follow through on something I’d been musing about for a while: building a full fledged VLOOKUP function in Power Query. Why? Yeah… that’s probably a...
In my last post I talked about useful text functions, and how they differed between Excel and Power Query. Today we’re going to look at another compare/contrast scenario, but this...
One of my favourite tricks to use on a dashboard is to show the Last Refreshed Date. This is important as it lets you know how stale your data is. ...
Years ago I published an article on my site called Five Very Useful Functions For Working With Text. This post is alternative version, but with a twist. I bring you...
Date formats in Power Query are one of those little issues that drives me nuts… you have a query of different information in Power Query, at least one of the...
One of the questions I get quite frequently is how we can pass parameters to SQL queries, allowing us to make them dynamic. Someone asked a question in the forum...
In the good old days, it was easy to check the application version in Office with VBA. You just used a little test of Val(Application.Version) to return the number. 12...
One of the cool features in Excel 2013’s Power Query is being able to load to the Data Model (PowerPivot) directly. But Excel 2010 doesn’t appear to have this feature. ...
After one of my previous sorting posts, Devin asked if we can number rows by group. Actually, that's a paraphrase… what he really asked was: Any thoughts on how to...
The term “Join” comes from the database world, and I’ll admit that I’ve struggled with understanding it... especially when you combine it with some other keywords. So this week I...
Some time back I posted a routine to refresh Power Queries with VBA, allowing you to refresh all Power Queries in a workbook. Things Changing… The challenge with that specific...
I was presented with an interesting problem last week: how to create running totals in Power Query. There doesn’t appear to be a built in method to do this, so...
In my last post, we looked at creating an IF statement using Power Query. This time we’re going to go a bit deeper and look at multi condition logic in...
The other day as I was working through a model, I once again tripped upon the fact that Power Query’s Text.Trim function doesn’t clean whitespace inside the text string, only...
