Getting Data from Multiple Sources in Power BI

Introduction

Let’s be honest, Power BI dashboards can look really pretty. But if the data behind them is messy, incomplete, or just plain confusing, then congratulations… you’ve built a very attractive lie.

At the heart of every solid Power BI report is one thing: good data coming in the right way.

In the real world, your data is never sitting nicely in one place waiting for you. Nope. It’s scattered everywhere, Excel files from one department, CSVs from another, a database somewhere, maybe even a random PDF someone swears is “the source of truth.”

This is where Power BI earns its paycheck.

With its Get Data feature and Power Query, you can pull in data from multiple sources, clean it up, and actually make sense of it, all in one place.

In this guide, we’ll walk through how to:

Connect Power BI to different types of data sources without stress Use Power Query to preview and understand what you’re working with Catch data issues early (before they embarrass you later) Set up a clean foundation for proper analysis and reporting.

Architecture Overview

Before we start clicking buttons, let’s quickly understand what’s going on behind the scenes.

Think of Power BI as the central hub where all your scattered data finally comes together to behave.

Here’s the simple breakdown:

Power BI Desktop → where you build your reports and models Data Sources → where your data lives (and misbehaves), such as: Excel and CSV files SQL databases JSON files and APIs PDFs (yes, even those stubborn ones) SharePoint folders

All this data flows into Power Query, which is basically your “data cleaning assistant.”

This is where you:

Preview your data Fix errors Transform messy columns And make everything analysis-ready

Only after that does the data get loaded into your model, where the real magic (and dashboards) happen.

Before we start clicking buttons, let’s quickly understand what’s going on behind the scenes.

Think of Power BI as the central hub where all your scattered data finally comes together to behave.

Connecting Data from Multiple Sources

Now to the real work, actually getting the data into Power BI.

Power BI connects to many data sources, which is great… until you realise you might need to connect to all of them in a single project. Don’t worry, once you understand the pattern, it becomes pretty straightforward.

Let’s walk through the common ones you’ll definitely run into:

Step 1: Connecting to Excel

This is usually where everyone starts nice, familiar, and (mostly) well-behaved.

Open Power BI Desktop Go to Home → Get Data → Excel

Select your Excel file

In the Navigator, choose the sheet or table you need

Click Load or Transform Data

If the Excel file is messy (and it usually is), just go straight to Transform Data and fix it before loading. Save yourself future stress.

Step 2: Connecting to Text/CSV Files

CSV files look simple… until you open them and everything is in one column.

Go to Home → Get Data → Text/CSV

Select your file

Preview the data

Click Load or Transform Data

Always preview properly here—wrong delimiters can ruin your day.

Step 3: Connecting to PDF

Yes, Power BI can pull tables from PDFs. And yes, it feels a bit like magic.

Go to Home → Get Data → PDF

Select your file

Choose what you need

Click Load or Transform Data

Just a heads-up: not all PDFs behave nicely, so expect to do some cleaning.

Step 4: Connecting to JSON

JSON is common when working with APIs, and it can look… intimidating at first.

Go to Home → Get Data → JSON

Select your file or API endpoint

Load into Power Query

Expand nested fields to make the data readable Click Close & Apply

The key here is mastering the “expand” feature—otherwise, you’ll just be staring at records inside records forever.

Step 5: Connecting to SharePoint Folder

Perfect for team environments where files live in the cloud.

Go to Home → Get Data → SharePoint Folder

Enter the SharePoint URL

Authenticate if needed

Select your files Click Combine & Transform Data

This is super useful when files are updated regularly—you don’t have to reconnect every time.

Step 6: Connecting to MySQL Database

Now we’re stepping into database territory.

Go to Home → Get Data → MySQL Database

Enter server and database details

Provide credentials Select the tables you need Click Load or Transform Data

At this point, you’re not just importing data—you’re choosing what actually matters.

Step 7: Connecting to SQL Server

Very similar to MySQL, just a bit more common in enterprise setups.

Go to Home → Get Data → SQL Server

Enter the server name

Choose authentication

Select your database and tables

Click Transform Data

This is where your SQL knowledge really starts to shine, especially if you decide to write queries instead of loading full tables.

Step 8: Connecting to Web Data

For pulling data directly from websites or APIs.

Go to Home → Get Data → Web

Enter the URL

Select the detected data

Click Load or Transform Data

Very useful, but also where things can break easily if the source structure changes.

Step 9: Connecting to Azure Analysis Services

This is more on the advanced/enterprise side.

Go to Home → Get Data → Azure → Azure Analysis Services

Enter the server

Select your model Choose Live Connection Click Connect

Here, you’re not importing data, you’re connecting directly to an existing model.

If you’re noticing a pattern, you’re right: Connect → Preview → Transform → Load

Once you get comfortable with that flow, switching between data sources becomes second nature.

Conclusion

Connecting to multiple data sources in Power BI isn’t just a setup step, it’s where everything begins.

Because the truth is, your dashboard is only as good as the data behind it. And in real-life scenarios, that data is coming from different places, in different formats, and sometimes in very questionable conditions.

Power BI makes this process manageable, especially with Power Query acting as your cleanup zone. It gives you the chance to actually understand your data before jumping into visuals.

But here’s the important part: connecting data is easy; preparing it properly is where the real skill is.

When you take the time to:

catch inconsistencies early handle missing or messy values and shape your data properly

you end up with reports that people can actually trust.

And that’s the goal, not just pretty dashboards, but reliable insights.

At the end of the day, good analysis doesn’t start with charts. It starts with how well you bring your data together in the first place.