Blog • Page 6 of 24 • My Online Training Hub

November 11, 2021

Remove Text Between Delimiters – Power Query

This is my source data and I want to remove the percentages Specifically I want to remove all occurrences of

October 28, 2021

Tips for Using The Power Query Advanced Editor

If you've only ever used the user interface (Ribbon and menus) to create your transformations then you're missing out on

October 21, 2021

Hash Sign in Excel Formulas

If you’ve worked with dynamic array formulas*, you’ll have noticed that they’re able to spill the results to multiple cells.

pivot unknown variable number of rows to columns

October 7, 2021

Pivot an Unknown Number of Rows into Columns

Sometimes you have a table of data that describes a 1 to many relationship. The number of values on the

September 30, 2021

10 Common Excel Mistakes to Avoid

I’ve received a lot of Excel files from our members over the years and I see the same mistakes time

September 16, 2021

Cool New Features in Excel for Microsoft 365

In this tutorial I’m going to highlight some cool new Excel features that might have slipped under your radar, a

August 26, 2021

Dynamic Dependent Data Validation

Before we had the luxury of dynamic array functions, creating dependent data validation lists typically required using multiple tables and

August 19, 2021

IFERROR in Power Query Using TRY OTHERWISE

In Excel we can use IFERROR to if our calculation results in an error, and we can then tell Excel

August 5, 2021

Easily Compare Multiple Tables in Power Query

Comparing table columns in Excel is a common task. You may need to identify items that are the same, different,

July 29, 2021

VLOOKUP in Power Query Using List Functions

If you've done lookups in Power Query to pull values from one table into another, you may have used a

July 22, 2021

Auto Refresh PivotTables

In this tutorial we’re going to look at the options we have available to auto refresh PivotTables in Excel without

July 15, 2021

Power Pivot Running Total

In this tutorial we’ll look at how to create a Power Pivot running total that allows you to see the

July 8, 2021

Secret Power Query Function List

Hidden inside both Excel and Power BI is a secret Power Query function list that you can bring up while

June 16, 2021

Show Items with no Data in PivotTables

Show Items with no Data in PivotTables is a handy setting that allows you to show items in the PivotTable

June 10, 2021

Fuzzy Matching in Power Query

Fuzzy matching is the ability to match non-identical text based on how similar one string is compared to the other.

May 26, 2021

Handling HTTP Errors in Power Query and Power BI

If you are working with web servers, either because you are trying to scrape data or you are using a

May 20, 2021

Non-financial Excel Dashboards

Dashboards give a visual snapshot of your business performance. They typically show data spread over time and grouped by category.

extract characters from strings in power query using text select and text extract

May 13, 2021

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

In this post I'm going to use the Text.Remove and Text.Select functions in PQ to extract characters from text strings.

May 5, 2021

Share and Collaborate in Excel

We’ve been able to share and collaborate in Excel files simultaneously with our co-workers for quite some time. However, recently

April 29, 2021

Highlighting Data in Power BI Visuals

In this post I'll be looking at ways to highlight or label values that may be of special interest in

April 27, 2021

Secrets to Building Excel Dashboards in Under 15 Minutes

Building Excel Dashboards doesn't need to be a slow process. When your data is structured correctly you can use the

April 21, 2021

Power BI Organizational Data Types in Excel

Power BI Organizational Data Types in Excel allow you to store attributes about your data in a single cell. This

April 15, 2021

Shape Maps in Power BI

In this post I'm going to show you how to use Shape Maps in Power BI, which are currently a

April 7, 2021

Excel Workbook Protection

When you spend days crafting your latest report, the last thing you need are well meaning users messing with your

March 31, 2021

Static Tables in Power Query, Power Pivot and Power BI

Ordinarily when you want to create a table in Power Query, Power Pivot or PBI, you'd write a query to

March 25, 2021

Change Type Using Locale with Power Query

One of the most common issues I help people fix is data imported to Excel or Power BI using Power

March 18, 2021

Get Data from OneDrive or SharePoint with Power Query

Nowadays many of us are saving our files to the cloud using OneDrive for Business or SharePoint Online. Unfortunately, it’s

March 11, 2021

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

When you deal with elapsed time in Power Query you can make use of the Duration data type. Howevere when

March 4, 2021

Recursive LAMBDA Functions

The new Excel LAMBDA function that we looked at last week also enables us to write custom recursive LAMBDA functions.

February 25, 2021

Excel LAMBDA Function

The new Excel LAMBDA function allows you to define your own custom functions using Excel’s familiar formula language. That means