OK, let’s get started with Power Query. The first thing we need to do is get data into Power Query. In this series, it is our first real look at Power Query; we are also going to use this as an opportunity to explore the user interface. This post is structured into two main parts: Read More
Category: Blog
Introduction to Power Query
The party was already in full swing; I was going to be late. Not fashionably late, just late. When I finally arrived, rather than the usual “What time do you call this?“, I received the “Hey; you made it, that’s amazing. It’s great you could come. Let me show you around“. Nobody looked down at Read More
VBA Rename File: 5 code examples
VBA allows us access to the windows environment to rename a file. In this post, we will look at 5 examples of renaming files with VBA. Rename a File VBA To rename a file with VBA we use the Name command. Example 1: Renaming a file This example renames a file from Example File.xlsx to Read More
Remove excess spaces in Power Query
I showed various methods to remove additional spaces in Excel in a previous post. One of the methods involved creating a custom function in Power Query. As this is a more advanced method, I wanted to go a bit deeper and describe the method to remove excess spaces in Power Query. Excel TRIM vs. Power Read More
CSV to Excel: Power Automate & Office Scripts
CSV files are a common file format; they are regularly used to export data from IT systems. Excel desktop can convert CSV to Excel with ease. However, in the online world of Office 365, things aren’t quite so simple. The online world provides us with new automation opportunities not available in a desktop environment. As Read More
7 ways to remove additional spaces in Excel
The space character for an Excel user can be a real problem. When exporting data from various IT systems, that export will often include extra spaces around values. Finding and removing additional spaces is a crucial skill for every Excel user. We often can’t see the space characters, but Excel sees them. This can cause Read More
Using Slicers with dynamic array formulas in Excel
A few years back, I created a YouTube video about using slicers with formulas in Excel. It was a pretty popular video. That method used a dummy PivotTable, to act as the criteria for the formulas to calculate on. Today, I want to bring you a cleaner option for using slicers with formulas. This method Read More
Weighted Average in Excel
The weighted average (also known as the weighted mean) is a calculation that provides an average where each value does not carry an equal impact on the final result. Using Excel’s AVERAGE function in these scenarios would give a misleading result. Thankfully, we can easily calculate the weighted average in Excel too. When to use Read More
Power Query – Connect to OneDrive Personal files
I shared how to get data from OneDrive or SharePoint into Power Query in a previous post. Many people noted that the method shown in that post works with OneDrive for Business but doesn’t work with OneDrive Personal. So, in this post, I will demonstrate the technique needed to connect Power Query to files in Read More
Power Query – Connect to OneDrive or SharePoint
In Power Query, when we connect to a local file, it’s reasonably obvious how we do that; it’s built directly into the UI. We just need to navigate to the location… Bam. Done! However, what if we want Power Query to connect to a file on SharePoint or OneDrive? How do we do that? It’s Read More
