Combining Data Sources (Video)
Start course

Power BI has changed the BI landscape forever, enabling BI professionals and regular Excel users alike to work with big data and build insightful dashboards. 

Learn to use this powerful business intelligence solution from the ground up. Navigate the intuitive user interface and explore the ecosystem of data modeling tools. Discover outside-the-box visualizations and broadcast your insights to colleagues in the Power BI Service. This Course gives you a solid foundation to begin your Power BI journey. 

Learning Objectives

On completing this Course, learners will be able to:

  • Identify the primary components of the Power BI interface: reports, data, and model views
  • Import Excel data and build basic visuals
  • Publish a desktop report to the Power BI Service
  • Identify common challenges in Power BI data models, implement smart solutions, and avoid common mistakes

Intended Audience

  • Business professionals whose job requires them to design, build, or deliver business intelligence metrics
  • Anyone preparing to take the Microsoft PL-900 exam


A desire to learn to use Power BI


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.

About the Author

Chelsea Dohemann is a Senior Technical Trainer and Microsoft Certified Master with almost a decade of experience in technology training. She has taught an array of applications from Microsoft products including Office 365 web apps, Microsoft Office Suite, Power BI, VBA for Excel, and SharePoint to Adobe Acrobat Pro and Creative Cloud. Being a persistent learner herself, Chelsea is acutely in-tune with the challenges of learning. She presents her topics in plain language, with real-world examples, reducing complex concepts down to their simple parts.