Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
When you hover your mouse over an element in a visual, like a column in a clustered column chart, a tooltip like this appears. The mouse is over the first orange column so the tooltip is showing the country, year, and sale total for that column. The country could be removed as you can tell …
Conditional formatting in Power BI visuals allows you to draw attention to, or highlight, data in text or numeric fields using color, icons or data bars. You can also assign URL's to fields to make them clickable and load web pages. Let's look at how to apply all of these different types of conditional formatting. …
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 …
Writing DAX is hard enough. If there were some way to make the experience a little easier wouldn't that be great? To that end, below is a list of keyboard shortcuts you can use in the Power BI Desktop DAX Editor. Watch the Video To See What These Shortcuts Do Download DAX Editor Shortcuts PDF …
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 …
Typically in Power BI visuals like a matrix or a table, and in pivot tables in Excel, we expect the totals to be the sums of the individual rows. This is probably because we are so used to adding up columns in Excel. In this example image (below) I've written a measure called [Target] to …
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 …
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 …
In this post I'll be looking at ways to highlight or label values that may be of special interest in visuals. Perhaps you are interested in seeing the minimum and maximum values, or some data points of significance like when your business held a promotion. Or you might want to highlight points that fall within …
In this post I'm going to show you how to use Shape Maps in Power BI, which are currently a preview feature. Typically, Shape Maps are used to illustrate the variation of a variable across a geographic area like a country or state. But you can also create custom shape maps for things like buildings, …
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 …
When you deal with elapsed time in Power Query you can make use of the Duration data type. Howevere when you load this into the Data model in either Power Pivot or Power BI, these durations are converted to decimal values. This post looks at how to use DAX to convert these decimals into human …
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 …
You may be familiar with Custom Format Strings in Excel, if you are not then check this Excel Custom Number Format Guide. What you might not know is that you can also use custom number formats in Power BI, and not only can you format columns of data, these custom formats can be applied to …
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 …
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 …
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 …
