Articles
Looking for something specific? Search here for relevant content.
Most Recent Posts
Get Data from Password Protected Access Database
If you've tried to use Power Query to get data from a password protected Access database, you'll know that it doesn't go well. Using the standard From Microsoft Access Database...Read More >>
Hide the Copilot Chiclet
It honestly pains me to have to write an article on how to hide the Copilot chiclet, but I find this thing far too intrusive. If you have a Copilot...Read More >>
Force Office to Open Files in Desktop Apps
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...Read More >>
Proving Excel’s Box and Whisker Chart Calculations
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,...Read More >>
Building a Box and Whisker Plot in Excel
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...Read More >>
Reading Excel’s Box and Whisker Chart
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...Read More >>
Convert Julian Dates to Gregorian Dates with Power Query
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...Read More >>
Get Album and Track Data from Spotify
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...Read More >>
Connect to Spotify using Power Query
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...Read More >>
Label Duplicates with Power Query
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...Read More >>
Master Your Data is Now Available!
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...Read More >>
Master Your Data with Excel and Power BI
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...Read More >>
Building a SelectQuery Function
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...Read More >>
Major redesign at Skillwave.Training
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...Read More >>
New Monkey Tools Features
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...Read More >>
More free features in Monkey Tools
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...Read More >>
Introducing the Measure Monkey
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...Read More >>
Status of our Master Your Data Book
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...Read More >>
Use Excel Tables to Filter a Power Query
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...Read More >>
Update to Monkey Tools QuerySleuth
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...Read More >>
Most Commented Upon Posts
Building a Parameter Table for Power Query
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...Read More >>
Power Query Errors: Please Rebuild This Data Combination
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...Read More >>
Refresh Power Query With VBA
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...Read More >>
Using Power Query with Email
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...Read More >>
Combine Multiple Excel Workbooks in Power Query
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...Read More >>
Activating a Tab on the Ribbon
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...Read More >>
Creating a VLOOKUP Function in Power Query
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...Read More >>
The IF Function in Power Query
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...Read More >>
Display Last Refreshed Date in Power BI
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. ...Read More >>
5 Very Useful Text Formulas – Power Query Edition
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...Read More >>
Date Formats in Power Query
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...Read More >>
Pass Parameters to SQL Queries
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...Read More >>
Check the application version in Modern Office
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...Read More >>
Load Power Query directly to Power Pivot in Excel 2010
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. ...Read More >>
Number Rows by Group Using Power Query
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...Read More >>
Create Running Totals in Power Query
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...Read More >>
Update: Refresh Power Queries With VBA
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...Read More >>
Merge Tables using Outer Joins in Power Query
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...Read More >>
Multi Condition Logic in Power Query
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...Read More >>
Clean WhiteSpace in Power Query
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...Read More >>
