• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Philip Treacy

custom tooltips in power bi

Custom Tooltips in Power BI

June 2, 2022 by Philip Treacy

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 …

Read moreCustom Tooltips in Power BI
conditional formatting tables and matrices in power bi

Conditional Formatting in Power BI Tables and Matrices

May 19, 2022 by Philip Treacy

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. …

Read moreConditional Formatting in Power BI Tables and Matrices
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

April 21, 2022 by Philip Treacy

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 …

Read moreRemove Blank Rows and Columns from Tables in Power Query
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

April 7, 2022 by Philip Treacy

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 …

Read moreExtracting Data from Nested Lists and Records in Power Query
dax editor keyboard shortcuts

DAX Editor Keyboard Shortcuts

March 17, 2022 by Philip Treacy

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 …

Read moreDAX Editor Keyboard Shortcuts
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

March 3, 2022 by Philip Treacy

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 …

Read moreCombine Files With Different Column Names in Power Query
fixing incorrect totals in dax

Fixing Incorrect Totals in DAX

January 20, 2022 by Philip Treacy

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 …

Read moreFixing Incorrect Totals in DAX
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

December 2, 2021 by Philip Treacy

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 …

Read morePower Query Keyboard Shortcuts to Save Time
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

November 11, 2021 by Philip Treacy

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 …

Read moreRemove Text Between Delimiters – Power Query
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

October 28, 2021 by Philip Treacy

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 …

Read moreTips for Using The Power Query Advanced Editor
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

October 7, 2021 by Philip Treacy

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 …

Read morePivot an Unknown Number of Rows into Columns
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

August 19, 2021 by Philip Treacy

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 …

Read moreIFERROR in Power Query Using TRY OTHERWISE
easily compare multiple tables in power query using list functions

Easily Compare Multiple Tables in Power Query

August 5, 2021 by Philip Treacy

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 …

Read moreEasily Compare Multiple Tables in Power Query
vlookup in power query using list functions

VLOOKUP in Power Query Using List Functions

July 29, 2021 by Philip Treacy

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 …

Read moreVLOOKUP in Power Query Using List Functions
fuzzy matching in power query

Fuzzy Matching in Power Query

June 10, 2021 by Philip Treacy

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 …

Read moreFuzzy Matching in Power Query
handling http errors in power query and power bi

Handling HTTP Errors in Power Query and Power BI

May 26, 2021 by Philip Treacy

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 …

Read moreHandling HTTP Errors in Power Query and Power BI
extract characters from strings in power query using text select and text extract

Extract Letters, Numbers, Symbols from Strings in Power Query with Text.Select and Text.Remove

May 13, 2021 by Philip Treacy

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 …

Read moreExtract Letters, Numbers, Symbols from Strings in Power Query with Text.Select and Text.Remove
highlighting data in power bi visuals

Highlighting Data in Power BI Visuals

April 29, 2021 by Philip Treacy

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 …

Read moreHighlighting Data in Power BI Visuals
shape maps in power bi

Shape Maps in Power BI

April 15, 2021 by Philip Treacy

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, …

Read moreShape Maps in Power BI
static data tables

Static Tables in Power Query, Power Pivot and Power BI

March 31, 2021 by Philip Treacy

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 …

Read moreStatic Tables in Power Query, Power Pivot and Power BI

Converting Decimal Time to Days, Hours, Minutes, Seconds in Power BI

March 11, 2021 by Philip Treacy

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 …

Read moreConverting Decimal Time to Days, Hours, Minutes, Seconds in Power BI
sort by column in power bi

Sort-By Columns in Power BI

February 18, 2021 by Philip Treacy

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 …

Read moreSort-By Columns in Power BI
custom formatting strings in power bi

Custom Formatting Strings in Power BI

January 28, 2021 by Philip Treacy

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 …

Read moreCustom Formatting Strings in Power BI
table.profile statistics in power query

Table Statistics from Table.Profile in Power Query

January 14, 2021 by Philip Treacy

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 …

Read moreTable Statistics from Table.Profile in Power Query
changing power query data source settings

Changing Data Source Location in Power Query

December 16, 2020 by Philip Treacy

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 …

Read moreChanging Data Source Location in Power Query
dense ranking in power query

Dense Ranking in Power Query

November 24, 2020 by Philip Treacy

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 …

Read moreDense Ranking in Power Query
Create a List of Matching Words When Searching Text in Power Query

Create a List of Matching Words When Searching Text in Power Query

October 29, 2020 by Philip Treacy

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 …

Read moreCreate a List of Matching Words When Searching Text in Power Query
Searching for Text Strings in Power Query

Searching for Text Strings in Power Query

October 22, 2020 by Philip Treacy

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 …

Read moreSearching for Text Strings in Power Query
grouped running totals in power query

Grouped Running Totals in Power Query

October 7, 2020 by Philip Treacy

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 …

Read moreGrouped Running Totals in Power Query
quickly create running totals in power query

Quickly Create Running Totals in Power Query

September 24, 2020 by Philip Treacy

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 …

Read moreQuickly Create Running Totals in Power Query
  • Go to page 1
  • Go to page 2
  • Go to page 3
  • Interim pages omitted …
  • Go to page 5
  • Next

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.