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
Category: Power Query
Get data from OneDrive or SharePoint with Power Query
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 get data from OneDrive or SharePoint? How do we do that? It’s not obvious. Read More
How to list all possible combinations from multiple lists (2 ways)
Imagine a scenario where you have two or more lists in Excel and from that, you want to list all possible combinations in one table. This is known as a cartesian join. The question might come in a simple form, such as: “How can we get a complete list of all General Ledger and Cost Read More
How to calculate Power Query Percent of total or category
In this post, we look at how to calculate the percentage of a total in Power Query. But we will also take this one step further, to consider how to calculate the percent of a category. In standard Excel, these calculations are simple because we are so used to the formulas to achieve this. For many, Power Read More
Power Query date format (How to + 5 tricky scenarios)
One thing that continually causes problems for all data professionals is dates. Over time, Microsoft has given us a plethora of tools and Excel functions for handling dates, yet they still catch us out. Power Query is a tool for manipulating and cleaning data, and it has a lot of features for managing dates. The Power Query Read More
How to remove spaces in Excel (7 simple ways)
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 a way to remove spaces in Excel is a crucial skill for every user. We often can’t see the space characters, but Excel sees them. They Read More
How to get data into Power Query – 5 common data sources
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
How to combine rows in Power Query
In this post, we look at how to combine rows with Power Query. The user interface gives us a way to merge columns, but there is no Power Query merge rows transformation. Therefore, I will show you how to achieve it in this post. There are two common scenarios in which we need to merge Read More
Common Power Query errors & how to fix them
I’m sure you’ve not got this far without encountering your fair share of Power Query errors. Just like Excel and other applications, Power Query has its own unique error messages. You’ve probably forgotten the first time you encountered the #NAME? or #VALUE! errors in Excel, but over time you hopefully worked out what to do when they Read More
How to use Power Query Custom Functions
In this post, we explore the topic of Power Query custom functions; What are they? When should we use them? And how to create them? At the time of writing, over 700 standard M functions are already available in Power Query. So why do we need custom functions at all? Custom functions use the existing M function Read More