Now we're not totally done because we actually don't have all the data we need. This data is missing some important details, and those remaining details are located in a separate file. Let's look at the common necessity of combining multiple data sources. This becomes necessary when we have data in multiple different places, but we want to bring that data together so we can visualize it on the same graphic.

Our first task is to query all the different data sources so they are all in Power BI. And then we will merge or append the data to bring it all into a single table. So let's start by creating another query to bring in the remaining data. If we were back in regular Power BI, we would find the same button as last time, but now we are in Power Query, so within Power Query, we can create new queries by clicking on New Source. And it's an Excel file again. This Excel file called Client Account Reps, and we'll click Open.

We're then going to select Sheet One, Let's just stop right now to give it a helpful name, and we're set. Now we have our two datasets but they could actually operate as one dataset or one table. So how can we combine these into one? There are two tools that helps us combine data sources. We have Merge and Append. Merge acts like a VLOOKUP, if you're familiar, matching row data between two tables and adding the data in as a new column on the right-hand side. Append, on the other hand, assumes your columns are similar enough that it can just paste the new data at the bottom of the existing data.

In our table, we want to match each client account with some matching information from the second data source, so we will use Merge. Now while we are merging, we have a couple options to help us stay organized. If we click this button to merge queries, we're gonna end up attaching the new data to this Customer's query, but if we want to keep everything tidy and separate, we can merge as new, which will create a new third query sep from the original two that contains all of the combined data. I like this option for training purposes to help us keep all the concepts separate, so let's go this route.

First, we need to select the two queries we want to combine. And then, we'll highlight the columns with matching data. There are many different join kinds, and it's worth reading the description to make sure you get what you want. I want all the client accounts from the first query regardless of whether they have a rep assigned to them, so I'll choose left outer join. And hopefully it will say we have, yep, we have 43 matches, but if there were one or two unmatched rows from either side, we could investigate and determine why before continuing on. Let's click OK, and there we go, we now have this new query here and we can rename this Client Account Info.

Let's just scroll to the right and find where the new information is gonna go here in this column. And now we get to decide which columns from the second query we want displayed here. If we just click on this little double headed arrow, we can choose to ignore customer ID because we already have it in the first query, but we do want the language, the CAM and the CAS. I'm gonna uncheck this box because I don't care to use the second query's name in the column titles. And then I'll click OK. This whole process takes a little practice, but once you've got the hang of it, it is a life saver. Let's return back to the normal area of Power BI by clicking Save and Close.