Quickly Change Multiple Queries, Same Source in Power Query

If you've created multiple queries from the same source and then the location of that source changes, you'd think that you would need to go into each query and one by one change the source location. If there's a few queries, it not so bad. If it's a LOT of queries, that will be painful. You can actually use a parameter and then tweak some source settings to let you change the source location all in the same time. Plus at the end of the video I'll show a simple design choice that could be used to mitigate all this without using a parameter.

Extract Text in Power Query

When you have some values to pull out of a text or numerical string in a cell, you can use the extract commands in Power Query to make it easy. This is an great option to use instead of using the RIGHT, MID or LEFT functions in Excel or trying to split the value by some delimiter. It's so much more easier to pull the data into the Power Query editor and using Transform and Extract to pull your desired text string out.

Reverse a String of Text with Power Query

Reversing text or a string of text seems like something that is common task in software development either as a entry into how to perform this task by writing code instead of using built in function (or it's an interview question!). It seems to be an simple task but thinking it through and putting it into code is something that helps engineers think about how to write code elegantly. How does Excel help in this? By using it to already reverse the text output, you can use Excel to help validate the output of the code. And using Power Query, it's really easy to get this output. Check out the video to see how.

Calculate Future Amount based on Changing Rates in Excel

If you were offered a guaranteed interest rate for a couple of years but the rates changed depending on the year, how would you know know what the future value would be? Say you're investing $1000 and for year one the interest rate is 5%, year two is 4.5% and year three is 4%. This is basically compounded interest on top of each year. It's fairly easy to do manually and even easier to do with the FVSCHEDULE function. Another fun way to use this function is to find out what historical figures would be worth today based on an inflation adjusted amount. The video will cover how this could be done.

Compare Economic Data and Stocks with CORREL Function

If you're doing any investing whether it be stocks, bonds or ETFs you're going to do research. One of the avenues of research is to look at macro economic data to help you forecast. One great repository of economic data is the St. Louis Federal Reserve. They actually have an Excel Add-in where you can get decades past data and it's all free! Couple that with stock data you can get from Google or Yahoo finance, and you've got all the data you need to do some correlation analysis with macro economic data and individual stock data.

Use Power Query to Unpivot Report with Multiple Column Row Header Fields

If you've ever gotten a report, it's already been summarized with a specific format with a set of column or row headers (or maybe multiple layers of column or row headers). You'd think that it would take some time with multiple copy and paste steps to get the report view back into a table. With Power Query, you can revert this report back into a proper table so you can do some further analysis in just a few steps.

Cut Out Image from a Background in PowerPoint

To remove an image from a background or the cut the background away on a picture you want to re-use somewhere else used to be a tasks that involved Photoshop skills, a subscription to some online editing software or a small consulting fee to a graphic artist. Now with the latest version of PowerPoint (Office or Microsoft 365) you can perform this image editing task with just a couple of clicks (depending on the complexity of the image). This video will show 3 examples of how to cut a picture away from it's background from the simple to a bit more complex. See the timestamps to start from the simple example to the more complex. 1 - Gift Box (1:06) 2 - Pumpkin (9:40) 3 - Athlete (14:58)