Power Query, dynamic tables, and financial modeling

- [Instructor] Working with data in models in Excel can be frustrating when you don't use dynamic functionality. It can require lots of rework, manual updates and testing to ensure that everything is correct. What if you could reduce that rework and get your FP&A models to update almost automatically? What if you could build FP&A models with strong integrity that are positioned to scale? Well, that's not wishful thinking. That's what I'm going to share with you here. This lesson is going to be a challenging one, so pay attention. But you're going to be mesmerized by what's possible. Let's explore the H+ Sports financial model. On this worksheet, you can see that I have a simple summary of monthly transactions. I have a control area right here in the top left that shows the start month, the end month, and the months in the model. Let's examine each one of these cells. Start month. Right here at D2, you can see that I've used a formula that says =MIN(transactions_YTD{EOMONTH}). Well, let's take a look over here at this dynamic table. If I put my cursor on the data, go up to Table Design, you'll note that this is named transactions_YTD. Right here in column G, I've created a column that is named EOMONTH. Not surprisingly, this is the last date of January. This is the last date of February. This is the last date of March and so on. So in other words, when we go to the financial model, what this is doing is it's taking a look at that entire range of dates and identifying which is the lowest or the minimum. As you can see, the minimum date is January 31st, 2024. End month, not remarkably different. So, here you can see it's taking the max date on the transactions_YTD EOMONTH column. If we were to go over here and go all the way down, you'll note that at the very bottom, I'm at September 30th, 2024. Not surprisingly, September 30th, 2024. And finally, I have a calculation for months in the model, which is calculating the difference between the end month and the beginning month, and adding one to show that we have nine. At the top of the model, I have a time series. X number of periods, Y number of months. And along the side in column B, I have a list of these salespeople. Now, how did I get to this list? Well, let me put my cursor right here. I'm using a UNIQUE and I'm using an XLOOKUP. XLOOKUP is a newer lookup function, somewhat similar to VLOOKUP, HLOOKUP, and INDEX match, but it's a lot easier to work with. What this is doing is it's looking at B9. In other words, it's looking at the word "salespeople". It's looking up the word "salespeople" within this dynamic range. Let's take a look at what this is. Excel is telling us that it is Order Number, Salesperson, Product, Region, Customer. Over here on the transactions_YTD, if I go all the way up to the top of my data, Order Number, Salesperson, Product, Region, Customer, Date. These are my dynamic headers. So, in other words, it's looking up the word "salesperson" with this range of dynamic headers. It's looking right here in column B. But that's not all. If I were to take this UNIQUE function out of here and hit Enter, what you're going to see is you're going to see a whole lot of redundancy and duplication. Why? Because it's taking a look at every single instance of the salesperson's name. Well, I don't want that. And obviously, when you take a look at these values to the right, that's giving me erroneous data. If I put a UNIQUE on the front, what this does is it says, "Let's take a look at the array and let's only display each instance one time." That's exactly what I want here. You want to see some magic? Let's go over to the transactions data. I'm going to go down to the bottom. I'm going to take one piece of data, put it in here, make sure that my dynamic table gets extended, and I'm going to temporarily put in my name. Well, what do you think's going to happen? If I go back over to the financial model, not surprisingly, there it is, all the way down at the bottom. Why does this look so strange? Why do I have no transactions from January to August, but 567 in September? Well, let's take a look. I have one instance, takes place in the month of September. 567 is my transaction amount. This automatically updates because this is a dynamic model. Let's take this out and get back to where we were. Okay, now you're going to love this even more. Watch what happens when I change the value at B9. I'm going to put my cursor here. You'll note that I have a dropdown. This pertains to every header, which is within my dynamic table. I'm going to select Customer. Instantaneously updates. This is still 1.85 million. When I changed customer over to product, look at what happens. All of my products appear, all of the correct data appear. Still 1.85 million. And let's do one more. Region: Northwest, Northeast, Southeast, Southwest. All of my data automatically updates for a total of 1.85 million. This is pretty amazing, right? Okay, let's keep going. At F10, I am using a SUMIFS formula. SUMIFS sums values where certain criteria are true. So, let's demystify what's taking place here. This is saying let's take a look at the dynamic table in the Total Cost column all the way over here to the right. Next, I'm performing the same XLOOKUP function. Take a look at B9, Salesperson within the dynamic headers and pull the correct column. Then, I want to look up a dynamic array of what is at B10 and below. Now remember, I used a UNIQUE function here. A UNIQUE function is a dynamic array, so this is constantly changing. When I put my cursor back here, it's saying, "Take a look at the top of the array as far as that array spills." So, find Cole Arnold within the correct column identified here for salesperson. And then finally, I have a second criteria range and a second criteria, transactions_YTD EOMONTH. What is that? It is this column right over here. The last date of every month. And I want to find where F7# this January, February, March, all the way to the end matches that date. Well, what is F7#? Let's take a look right here. This is also a dynamic array. This is saying take a look at the year, month, and end of that month for the start month here. I'm starting at January. It's increasing every increment. 1, 2, 3, 4, all the way to the end to the last month of my data. If I were to go to my transactions and sort this information only by September, then highlight all of this information and delete it. What do you think is going to happen to my model? I now only have data from January to August. When I go back over to my financial model, I now have data from January to August. Is this magic? No. These are dynamic tables with dynamic arrays. Let's put these back. Finally, I have a dynamic sum in row number 9. I'm using the BYCOL function. The BYCOL function says, "Let's take a look at a dynamic array," which is what we have here. And I just want to sum the values in that column. If I highlight all of this, you can see that I have 286, 286. Highlight this, 215, 215. So, as far or as narrow as this data set goes, my sums will dynamically update. And finally, right here, instead of a BYCOL, I'm using a BYROW. This is also taking a look at the dynamic array, and it's summing everything in this dynamic array. 154, 154, 242, 242. Dynamic functionality in Excel has completely changed the game for FP&A professionals and those who do financial modeling. If you've taken any of my other LinkedIn Learning courses, you know that I use these a lot. Dynamic data sets supported by Power Query combined with dynamic arrays makes FP&A modeling far more effective and efficient. This is the future of FP&A in Excel.