Working with multiple tables in Copilot

- [Instructor] Combining data from different places can be a daunting task that shakes the confidence of many Excel users. Here's where Copilot steps in to change the game. Let's take it for a spin with the exercise file, copilot-multiple-tables. In this example, we're looking at New York City's population by borough for each decade. The catch? It's sorted by borough ID and not by name. While using IDs here is computationally useful, it can get tricky when you're trying to work with the data in a practical sense. This is where Copilot can really shine, both as a short-term helper and a long-term guide to efficiently managing multiple tables. Let's put it to the test. So I'll open up Copilot. Let's go to App Skills. I'm going to select the population table on the left of the worksheet here. Just put my mouse anywhere there. And I'm going to ask for the population of Brooklyn in 1990. (keyboard clacking) Now, without the label of Brooklyn in this data set, this could be difficult to figure out on our own. Copilot should be able to cross-reference our population data here with the label data to the right. Now, this is using Python to get the result here. And again, if we don't want Python, we can ask for something else. But I do want to double-check this is the result. So Brooklyn is borough number two, and we are looking for the data in 1990. So that is 2 million. So that was correct. Now, if you don't want Python, you can use something else. But let's go ahead and actually specifically, in the next prompt, ask for some Excel formulas. So rather than asking for a single data point, this time I'll ask Copilot to draft an XLOOKUP to pull details from the entire Boroughs table. We'll be specific here. We want to return borough from the Boroughs table based on borough ID. I'm going to put my mouse in our population table, and let's run that prompt. And we do get a formula here. It looks like we are left to our own to add that. I'll go ahead and do that. (keyboard clacking) And we do have a population label there. And again, if we wanted to name this borough, we could do that. Now, this does assume that you're somewhat familiar with how XLOOKUP works, that you know about it. So this really highlights the need to have a solid Excel foundation before diving into what Copilot can really do. So, I'll go ahead and check our results here. And it looks pretty good, except there is one error here down at the bottom. It looks like there's a borough ID number six that's just not found in our LOOKUP table there. So what we can do here is we'll run another prompt. We're going to write XLOOKUP. We're going to specify something even more here if there's not a match what to do, return other. And there is our updated formula. I'll go ahead and just copy that right over here. And that is now coded to an Other. So, what if you wanted to use maybe relational joins in Power Query, instead of an XLOOKUP, to combine these sources? Well, Copilot can't automate our Power Query. It can help us go through the process with a pretty good amount of accuracy. So, I'm going to provide a new prompt here. How would I do this in Power Query? And I want to specify what tables I want, what join column I want, and so forth. And we'll see what kind of guidance we can get here. And here, we do have a step-by-step guide on how to make that inner join happen if we wanted to do that. In the beginning, Copilot was limited to working within a single table. However, you're seeing now, it's savvy enough to handle multiple tables across our workbook, bring it closer to everyday realities of many analysts who rarely have all their data neatly in one place.