Fuzzy Match Lookup in Power Query

https://youtu.be/aOl944MvkFc

If you’re familiar with doing a lookup in Power Query, it’s basically using the Merge feature. Inside this merge feature is a almost hidden option of using something called Fuzzy match.

If you’re used to doing lookups in Excel with VLOOKUP or INDEX/MATCH, you know that it does an exact match with the looked up text or value. What Fuzzy match does for your is to try to “guess” your looked up value and bring back your intended results. It’s neat that it would guess this and in some case with the help it can look up MSFT and bring back data associated with Microsoft (all spelled out).

Transpose One Column Into Multiple Columns with Excel Macro

Say you’ve got a file from someone that is an address list. Name, address, city etc. But it’s all in one column. It’s not separated into different columns to easily do any analysis or mail merge. You need to put it into a table with a name field or address field. This also can apply to other types of one column data or databases. There’s a bunch of ways to transform this type of data, and in this video, I’ll show a quick way to do it by recording a macro. It’s not as scary as it sounds. Heck I’m not VBA expert and I could even do it. And if I can do it, so can you.

Pivot Table Conditional Formatting

Pivot Tables are one of the best analytical tools in Excel. BUT like any other tables in Excel, you can get lost in the numbers and letters, when there’s a lot. And the easiest way to get your point across is with visuals. And that what conditional formatting can do for you. Help you communicate an important message with your data. You don’t always need to create a separate bar or line chart for your data. Even though a Pivot Table is a great analytical tool, sometimes you need an extra push to help highlight data and conditional formatting is something that does that.

Create a Custom Sort in Excel and Power Query

https://youtu.be/7PVjvY4a_V8

You can create a custom list to sort if you don't want to use the ascending (A-Z) or descending (Z-A) sort. If you want to sort North-South-East-West in this order instead of alphabetically (i.e., East-North-South-West), a creating a custom sort will be your solution. This video will show you how it's enable to help analyze Pivot Table data and also if you want to create a custom sort capability in Power Query.

Get Age or Years in Service with Power Query

Trying to figure out the age of a person based on their birthdate. Or you want to know how long someone has worked at your company or organizations. If it one to three people you're calculating this for, it's easy to manually do it. But it there is a long list of names on a spreadsheet and you've got to maintain it on a long term basis, there should be a more automated way to do this. This video will show you two general ways to do it ; one example using functions and another using Power Query.

Create a Probability Impact Table & Matrix

https://youtu.be/cbiuV-UWvPc

The video covers how to create a probability impact table or Risk Assessment table. If you're doing project management or risk management, you'd be creating this table or matrix chart for your program or projects. You'd need to list out the different risk criteria and then figure out the likelihood of the risk happening (the probability part) and then the impact it would have (high, medium or low). This combination of probability and impact then helps determine the risk (again high, medium or low) that specific risk has to the project. There's bonuses to this; in addition to how to create the table and risk matrix I show how to create a drop down list, use conditional formatting for the color coding and show how to count up the different combinations of probability-impact in the matrix.

Format Excel Sheet to Print a More Readable Table

Printing out excel ranges should be a simple process. It's just text or values in little boxes, but if there's little or no formatting your audience might find it hard to read. Unlike text in a book there may not be enough space to help make it easier to read. With some simple steps you can help make a table easier for your audience to read when you actually give them a hard copy printout to read. I'll give you two example of how this can be done.