Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
Before we had the luxury of dynamic array functions, creating dependent data validation lists typically required using multiple tables and named ranges, as shown here. Setting them up was laborious, however now that we have dynamic array functions, creating dynamic dependent data validation lists is much easier. Note: this technique requires a Microsoft 365 license …
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 …
In this tutorial we’re going to look at the options we have available to auto refresh PivotTables in Excel without using VBA. The process differs depending on whether you use Power Query to get the data or not. I’ll also show you the VBA method required for regular PivotTables. Beware because not all PivotTables based …
In this tutorial we’ll look at how to create a Power Pivot running total that allows you to see the aggregation at the month, quarter and year levels enabling you to create charts like this: Note: parts of this tutorial require Power Pivot in Excel or Power BI. Check if your version of Excel has …
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 …
Show Items with no Data in PivotTables is a handy setting that allows you to show items in the PivotTable even if there isn’t any data for them. For example, the PivotTable and chart below includes the East and West regions even though the Sum of Count is blank: Watch the Video Download Workbook Enter …
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 …
The Excel Quick Access Toolbar or QAT as it’s also known is not only a handy place for commonly used icons, but it also enables some super easy keyboard shortcuts for your favourite tools. The QAT sits either above or below the ribbon. I prefer to place it above the ribbon as this takes up …
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 …
Dashboards give a visual snapshot of your business performance. They typically show data spread over time and grouped by category. The most common dashboard examples are based on financial data, so in this video tutorial I’ll show you how to build non-financial Excel dashboards. I’ll be including some techniques I haven’t demoed in any of …
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 …
We’ve been able to share and collaborate in Excel files simultaneously with our co-workers for quite some time. However, recently there have been some significant improvements to the Excel co-authoring experience that will blow you away. All you need to co-author in Excel is a Microsoft 365 subscription and an internet connection. You can even …
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 …
Building Excel Dashboards doesn't need to be a slow process. When your data is structured correctly you can use the built in tools (that you probably already know), to build dashboards super fast. Let me show you how in the video below. Download Workbook Enter your email address below to download the sample workbook. Get …
Power BI Organizational Data Types in Excel allow you to store attributes about your data in a single cell. This rich data can then be referenced with the formulas we know and love. Imagine having a single cell that contains everything you need to know about a product or employee or a company, to name …
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, …
When you spend days crafting your latest report, the last thing you need are well meaning users messing with your file. In this tutorial I’m going to show you how to use Excel workbook protection which will allow your users to experience the report as you intended. They can: Click Slicer buttons Refresh data connections …
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 …
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 …
The new Excel LAMBDA function that we looked at last week also enables us to write custom recursive LAMBDA functions. Recursion is simply when a routine calls itself. That said, it can be a bit mind bending, so in this tutorial we’ll look at an example that’s easy to follow. Once you understand the structure …
The new Excel LAMBDA function allows you to define your own custom functions using Excel’s familiar formula language. That means we no longer need JavaScript or VBA programming knowledge to create our own functions. This is a huge step forward and for those familiar with the concept of lambdas it’s worth noting that the LAMBDA …
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 …
Excel pyramid charts are easy to build using bar charts or Conditional Formatting, but there are a few rules you must follow depending on the approach you choose. Before we look at the steps, keep in mind that these charts aren’t limited to visualising gender splits. They’re also handy for any type of demographic data …
Keeping track of your share trading is easy with this personal stock portfolio dashboard. It uses the latest Excel tools* including Data Types and the STOCKHISTORY function to create a report that’s connected to live data and updates at the click of a button. *Note: requires Microsoft 365. Not suitable for Excel 2019 and earlier. …
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 …
Shading or highlighting periods in Excel charts can help users more quickly interpret them and identify patterns. In the chart below I’ve highlighted every second month to give a quick visual indication of each period, which allows the user to focus on the line instead of having to refer back and forth to the horizontal …
