Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
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 …
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 …
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, …
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 …
Demand for data visualisation skills is showing no signs of slowing. There are many BI tools available, and this can make knowing which one to learn overwhelming. Global research and advisory firm, Gartner, has recognised Power BI as the Magic Quadrant Leader in analytics and business intelligence platforms for 13 consecutive years. This is just …
If you need to plot data that has one variable where values can be the same or very similar, for example the age of a group of people, you'll likely end up with data points that are plotted over the top of each other. To make this type of plot easier to read and allow …
In this post I'm going to look at getting real time data (RTD) into Power BI using a real time messaging service called PubNub. This is intended for use with the Power BI online service, not Power BI Desktop. Power BI provides a few different ways to get RTD : Push Data, Streaming Data and …
