Monday, January 23, 2023

Combine and Unpivot Tables Multiple Worksheets Power Query Function

When you have multiple worksheets or tabs to combine together, it's not a difficult process to copy and paste. However if you need to transform the table in those worksheets, like unpivoting it; still is not that bad albeit it'll take a bit more work to transpose the columns and rows. Now if you had to do this to a LOT of worksheets or if you had to do this on a recurring basis, it becomes a pain to do this. The solution is to have Power Query automate these steps; and it can future proof it by incorporating new data.

Monday, January 16, 2023

Use Power Query Parameter to Quickly Filter

This video will introduce you to using the parameter feature in Power Query. You can use parameter to pass values to other parts of your query to give interactivity to your query. This example will use a simple procedure of filtering a column to show how it works. Now filtering a column seems like overkill, but if you have a LOT of columns, scrolling across the many columns can be a hassle. Incorporating the parameter feature may make it less of a hassle.

Monday, January 9, 2023

Running Total in Power Query

Creating a running total in Excel is easy, really easy if you're an intermediate Excel user. Why then would we consider doing this in Power Query? Maybe it's a small part of a series of steps you're doing to clean up data. In that case you don't want to have a running total calculation outside of your Power Query steps. Unfortunately there is no Ribbon command in the Power Query editor to perform running totals. Don't worry, it's actually quite easy to do a running total in Power Query. It's just some M code and it's not even a lot.

Monday, December 19, 2022

Sharing Files with Power Query Parameter Feature

When you are sharing Power Query files, it's usually not an issue to update and refresh your data. However if your source file and your file that is the final output for analysis are separate files, it becomes an issue. You're sending two separate files to your co-workers and expect this to work? Good luck, unless you have an easy way to make your links to the source file link. That could be done fairly easily with a parameter. Check out the video to see how you can make sharing Power Query files seamless.

Monday, December 12, 2022

Calculate Difference from Previous Row Excel & Power Query

When you’re compare period data like sales from one month to another, quite often you’ll want to know the difference between the months. In a standard Excel worksheet, this is super easy to do. When you get to a pivot table, also super easy but maybe not quite as intuitive...though it can still be done super easy. The same for Power Query, easy to do but not intuitive. Thanks for coming to this video because I’ll show you these three ways to do subtract value from a previous cell.

Monday, December 5, 2022

Pivot Two Columns to Multiple Columns with Power Query

If you’re familiar with Pivot Tables in Excel, there’s a pivot feature in Power Query. Why would you want to use this feature in Power Query? Well, if it something you need to need as part of you transformation steps in Power Query then you need to know it’s there. Or if it’s a step you do on a recurring basis, Power Query makes it easier to do it with a few clicks.

Monday, November 28, 2022

Fuzzy Match Lookup in Power Query

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