Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
Power Query if statements, nested if, 'if or' and 'if and' statements are a bit different to writing IF formulas in Excel, but once you get the hang of them they’re pretty easy. In fact, the Power Query IF formula, IF OR, and IF AND are even easier than the Excel equivalents. Table of Contents …
Power Query variables are a great way to store data that can be used multiple times and can be easily updated in one place. There are a few ways you can create variables. 1. From the worksheet, which means you don’t even need to open the query editor to update the variable. This is great …
When you import data to Power Query, it's not unusual to end up with a table containing blank rows and columns, like this There are a few ways to get rid of these 'blank' rows and columns, some better than others. Watch the Video Download Example Excel Workbook Enter your email address below to download …
You may have data that is contained in lists or records, or even lists inside records, or records inside lists. I've seen nested data like this come from API's or Sharepoint amongst other sources. In such cases you may want to extract only certain bits of data from the list(s) or record(s) and this blog …
I'm going to look at situations that appear to be fairly common: loading data into Power Query where the source data has columns with different names. For example, in the following file received in August 2021, I have data that relates to various items over a 12 month period. What the data is showing is …
Below is a list of time saving keyboard shortcuts that you can use in Power Query, in both Excel and Power BI. Watch the video to see me demonstrate how to use them, and download a PDF containing all shortcuts. Watch the Video Download Power Query Shortcuts PDF Enter your email address below to download …
This is my source data and I want to remove the percentages Specifically I want to remove all occurrences of (00%) so my data ends up like this There's no native function in Power Query to do this so I'll have to write my own code. Watch the Video Download Sample Excel Workbook Enter …
If you've only ever used the user interface (Ribbon and menus) to create your transformations then you're missing out on some neat things you can do by using the Power Query Advanced Editor. In this post I'd like to show you what's in the Advanced Editor, how you can write code in it and hopefully …
Sometimes you have a table of data that describes a 1 to many relationship. The number of values on the Many side is unknown and can vary. So writing Power Query code to pivot this can be tricky. Let's look at an example. The first column is a list of the office locations of my …
In Excel we can use IFERROR to if our calculation results in an error, and we can then tell Excel to produce a different result, instead of the error. Power Query doesn't have IFERROR but it does have a way of checking for errors and replacing that error with a default answer, it's called try …
Comparing table columns in Excel is a common task. You may need to identify items that are the same, different, or missing from these columns. In Power Query, table columns are lists and you can compare these lists using table merges. But merging can only be done on two tables at a time. If you …
If you've done lookups in Power Query to pull values from one table into another, you may have used a query merge to do this. Mynda has written previously on how to do an Exact Match in Power Query and an Approximate Match in Power Query Here I'll be showing you how to use List …
Hidden inside both Excel and Power BI is a secret Power Query function list that you can bring up while working in the Power Query editor window…that’s if you know the magic word. You can use this list much like you might use the Excel Function wizard or to create a custom function. Clicking the …
Fuzzy matching is the ability to match non-identical text based on how similar one string is compared to the other. You may have text that has been entered as the answer to a survey question, in which case you can't control what the respondent types. This can result in misspelling of words, for example, take …
If you are working with web servers, either because you are trying to scrape data or you are using a web based API, you will be sending and receiving data via HTTP. HTTP is the Hypertext Transport Protocol - it's just the name of the system used by web sites to transfer data. You use …
In this post I'm going to use the Text.Remove and Text.Select functions in PQ to extract characters from text strings. I'll show you how to extract letters, either uppercase or lowercase, and a mixture of both, and how to extract numbers, and I'll show you a really cool way to remove a wide range of …
Ordinarily when you want to create a table in Power Query, Power Pivot or PBI, you'd write a query to load it from an external source. But any time you have data that won't change (or changes rarely), you can use a static table. That is, a table that doesn't need a data source, it …
One of the most common issues I help people fix is data imported to Excel or Power BI using Power Query where the regional settings of the PC differ from that of the source data. Usually, the problem arises with dates. For example, data formatted dd/mm/yyyy imported on a PC with a mm/dd/yyyy date format. …
Nowadays many of us are saving our files to the cloud using OneDrive for Business or SharePoint Online. Unfortunately, it’s not straight forward to get data form OneDrive or SharePoint with Power Query, so in this tutorial I’m going to step you through the three scenarios: getting data from an individual file on OneDrive or …
Normal sorting behaviour is to sort based on a numeric value, either higher to lower or lower to higher. But what if you want to have a custom sort order, or you are trying to sort categorical data in a specific way. How do you do that? Let's look at an example. The school our …
It's often useful to know some quick statistics about our data. Things like the minimum or maximum values, or the distinct count of items in a column. These statistics and more can be quickly and easily obtained using the Power Query function Table.Profile. Watch the Video Download Workbook Enter your email address below to download …
Here's the scenario - you've created a query in Power Query that loads data from a source, either a file or a folder. You then move that data source, so how do you change the query to load data from the new location? Loading a Single Excel Workbook If you are loading data from a …
There are several ways to rank things, dense ranking is when items that compare equally receive the same ranking number, with subsequent items receiving the next ranking number, with no gaps between numbers, like this: Download the Excel Workbook and Queries in This Post The queries in this Excel file can be copied/pasted into the …
My previous post searched for text in columns and returned a true/false result if a match was found. This post goes one step further and lists the words found in the searches. NOTE: Theses queries will search for any text value including numbers or dates stored as text. Download the Excel Workbook With All the …
Let's say we have a column of text and we want to search it to see if each row contains a list of words. The source data that I'll be searching through is a table in Excel named TextTable, and the list of words I'm looking for are stored in another table named WordList.. If …
What’s the big deal about Power Query? Talk to those who have used it and they’ll tell you how amazing it is. Stories of automating tasks that used to take 3 hours now taking 3 minutes is not uncommon or an exaggeration. If you haven’t heard of Excel’s Power Query tool, or you’ve heard of …
In a previous post I looked at creating running totals in Power Query, here I'll be creating grouped running totals. I'll start with a table of data showing values for cities within different countries. Download the Workbook With Sample Query The queries in this Excel file can be copied/pasted into the Power BI Desktop Advanced …
You may already be familiar with Excel Data Types for geography and stocks, but with Power Query Custom Data types we can now create data types based on our own data. This enables us to organise our data into a single column and then extract and reference the underlying columns/fields using formulas. It’s a streamlined …
A running total (or cumulative sum) is when you add the previous value to the next, basically you're summing values every step of the way. Creating a running total in Excel is easy. Mynda has written about a couple of ways you can do it Running Totals in Excel Running Totals in Excel Tables But …
If you're familiar with using Excel functions (and of course you should be) then you'll know that a function typically takes some input and gives you an output. The input, for example, could be a range, a number or a piece of text. The output type will vary, the SUM function will output a number, …
