Data Analyst PowerBI Test Guide: Connecting Multiple Data Sources
5 Smart Ways to Connect Multiple Data Sources in Power BI (Data Analyst Test Guide)Background: Power BI tests used in analytics placements rarely give you one clean file. Candidates usually get a few sources, say a sales Excel sheet, a customer CSV and a product list from a database, and are asked to turn them into one working dashboard in an hour or so. The interviewer is not only watching the final chart. They are watching how you load, clean, combine and model the data. That is why knowing how to handle powerbi data sources is one of the quickest ways to separate a prepared candidate from an average one.There is a mistake that almost every beginner makes in these tests. They load all the files, drag a few fields onto a chart, and then wonder why the totals look wrong. The charts were never the problem. The data underneath was. Mismatched data types, duplicate keys and missing relationships quietly corrupt everything built on top of them.A good habit is to treat the first twenty minutes of any test as data work, not visual work. This guide covers five methods that help you do exactly that, and each one is a question you can realistically expect in a data visualization interview for analytics placements.1. Pick the Right Connector Before You Load AnythingPower BI can connect to Excel workbooks, CSV and text files, SQL Server, web pages, SharePoint folders and many other sources. On the Home tab, click Get Data and choose the connector that fits each file. It sounds obvious, but the choice matters. A folder connector, for example, can read twelve monthly files in one go, while loading them one by one wastes time you do not have.When the preview window opens, click Transform Data instead of Load. This small habit gives you a chance to look at what you actually received before it enters your model. In a timed test, that one extra click can save you a lot of debugging later.2. Clean Every Source in Power QueryPower Query is where most of the real work happens. Before building a single visual, remove blank rows, promote the first row to headers, fix data types, rename unclear columns and trim extra spaces. Two sources can look identical and still behave differently. Customer ID might be stored as text in one file and as a whole number in the other, and Power BI will refuse to match them without telling you why.Treat every query like a small checklist: headers correct, types correct, no stray blanks, clear names. Every step you apply is recorded in the Applied Steps pane, so the process repeats automatically when the data changes. If an interviewer asks how you cleaned your data, you can simply walk through those steps in order.3. Know When to Append and When to MergeThese two options get confused constantly. Append queries stack tables on top of each other, so they suit files with the same columns, like January, February and March sales. Merge queries join tables side by side using a matching column, which is very close to a SQL join. You can also pick the join type, such as left outer or inner.A simple rule helps: more rows of the same thing means append, more details about the same rows means merge. Before merging, check that the key column has the same data type in both tables and no hidden spaces. Many failed merges in tests come down to a single trailing space. Also remember that merging on a column with duplicate values can multiply your rows, and your totals will inflate without any warning.Here is a quick example. Suppose you receive sales files for three months and a separate file with product prices. The three sales files get appended into one table, because they share the same columns. The price list is then merged in using the product code, so every sale row can show its price. Doing it in this order keeps the logic easy to explain out loud.4. Build a Clean Model with RelationshipsAfter the data is loaded, open Model view and connect your tables. The aim is a star schema, with one central fact table such as Sales and dimension tables like Customers, Products and Dates around it (see Fig. 1). This layout keeps the model simple, fast and easy to explain. Relationships should usually be one-to-many with a single filter direction, flowing from the dimension table to the fact table.Fig. 1. Star schema in Power BI Model view.Avoid many-to-many relationships and two-way filters unless you can justify them. They can produce confusing or wrong totals, and the report will not show an error. Add a proper Date table too, since time intelligence functions depend on it. If an interviewer asks why your model looks the way it does, saying it follows a star schema is a strong and honest answer.5. Refresh, Test and Validate Your ReportBefore you submit, check that the report survives a refresh. Compare your totals with the original files, test every slicer, and make sure no visual shows blank values where data should appear. A report that looks good but cannot be refreshed is not really finished. Interviewers often ask what happens when a source file is replaced, and the answer is that Power Query reruns the saved steps on the new data.Spend the last few minutes of a test on checks rather than formatting. Verify one or two numbers by hand, confirm that your measures use the correct fields, and give each page a clear title. Small, careful checks tell the reviewer that you understand the data, not just the tool.A Simple 60-Minute Plan for a Timed TestHaving a rough schedule in your head stops you from spending forty minutes on a single messy file. The split below is only a guide, so adjust it to the size of your task.First 10 minutes: read the task carefully and connect to every sourceNext 15 minutes: clean each query in Power Query
