How to Add a New Data Source in Power BI

Bringing your data into Power BI is the first essential step toward creating insightful reports and dashboards. This article will guide you through the process of connecting to several common types of data sources, so you can stop wrestling with raw data and start building meaningful visualizations.

Why Connecting Data Sources Is Your First and Most Important Step

Before you build a single chart, you need raw materials. Power BI is incredibly versatile, allowing you to connect to hundreds of different data sources - from simple spreadsheets on your computer to complex cloud databases. The real power isn't just in visualizing one source, but in blending them together to get a complete picture of your business.

Imagine your sales data lives in a Shopify export, your marketing spend is in a dozen Google Sheets downloaded from various ad platforms, and your website traffic is sitting in Google Analytics. By themselves, each source tells a partial story. When you connect all three in Power BI, you can finally answer critical questions like:

• Which marketing campaigns are driving the most valuable customers?
• What is the true return on ad spend (ROAS) when you factor in product costs and shipping?
• How does a spike in website traffic from a blog post translate into actual sales?

Connecting your data is the foundational block for unlocking these kinds of insights. It turns isolated data points into a cohesive business narrative.

Getting Familiar with the "Get Data" Hub

Everything starts in Power BI Desktop in the "Get Data" section, located prominently on the Home ribbon. Clicking this single button opens up a world of possibilities.

When you click the "Get Data" dropdown, you’ll see a list of the most common data sources. If you click the main button icon or "More..." at the bottom of the list, a new window will pop up, showcasing Power BI's extensive library of built-in connectors. It can feel a bit overwhelming at first, so it helps to know they are organized into logical categories:

• File: For static files like Excel Workbooks, CSV (Comma-Separated Values) files, XML, and entire folders of files.
• Database: For connecting to structured databases like SQL Server, MySQL, and PostgreSQL.
• Power Platform: For connecting to other tools in the Microsoft Power Platform ecosystem, like Dataverse.
• Azure: For connecting to a wide range of data and analytics services hosted on Microsoft Azure.
• Online Services: A huge category that includes connectors for popular SaaS applications like Google Analytics, SharePoint, Salesforce, and many others.
• Other: A catch-all for other sources like web pages, a blank query, or Python scripts.

Step-by-Step: Adding Your First Data Sources

Theory is great, but let's walk through connecting a few of the most popular data types. We’ll cover an Excel file, a table from a webpage, and an online service like Google Analytics.

1. Connecting to an Excel Workbook

For many people, data analysis begins in a spreadsheet. This is the most common starting point for new Power BI users.

1. On the Home ribbon, click Get Data and select Excel Workbook.
2. A file explorer window will open. Navigate to the location of your Excel file, select it, and click Open.
3. The Navigator window will appear next. This is Power BI showing you a preview of the available data inside your workbook. On the left pane, you'll see a list of all the sheets and any officially formatted Tables within your file.
4. Click on a sheet or table name to see a preview of its data on the right.
5. Check the box next to the sheet(s) or table(s) you want to import into your report. You can select multiple items.
6. At the bottom of the window, you have two primary options: Load or Transform Data.

For now, click Load to bring the data in. You'll see it appear in the "Fields" pane on the right side of your Power BI canvas.

2. Connecting to Data from a Web Page

You can also pull data directly from tables published on websites. This is incredibly useful for grabbing public data like stock market information, sports statistics, or government datasets without needing to download a file first.

1. On the Home ribbon, click Get Data and select Web.
2. A small dialog box will pop up asking for a URL. Copy the full URL of the webpage that contains the table you want to analyze and paste it here. Click OK.
3. Power BI will analyze the page and present you with the Navigator window again. This time, it will display a list of all the HTML tables it found on that page. It will often find little layout tables too, so you may see more options than you expect.
4. Click on each suggested table in the left pane to preview it. Find the one that contains the data you're after.
5. Check the box next to it and, just like with Excel, choose either Load or Transform Data.

3. Connecting to an Online Service (like Google Analytics)

Connecting to a cloud service works a little differently because it often requires you to authenticate your account and then select the specific data points you want to pull via an API.

1. Click Get Data and choose More... at the bottom of the list.
2. In the Get Data window, select Online Services from the category list on the left, then scroll to find and select Google Analytics and click Connect.
3. You'll be prompted to sign in with your Google account. This process (often using OAuth) gives Power BI secure, permissioned access to your Google Analytics data. Follow the prompts to sign in and grant access.
4. Once connected, the Navigator window will appear, but it will look very different. Instead of showing clear tables or sheets, it presents folders of Dimensions and Metrics available from the Google Analytics API.
5. This is where things can get tricky if you aren't familiar with the data structure. You need to combine dimensions (like Date, Source / Medium, or Country) with metrics (like Sessions, Users, or Pageviews).
6. Start simple. Open a folder, like "Traffic Sources," and check the box for Source / Medium. Then, open a folder like "Session" and select Sessions.
7. As you select items, a data preview will generate on the right. Once you're happy, click Load or Transform Data.

This same general process of authenticating and selecting data applies to many other online services like Salesforce, Mailchimp, or Zendesk.

What’s Next? The Power Query Editor

When you select "Transform Data" after connecting to a source, you enter the Power Query Editor. This is a powerful tool-within-a-tool designed for data preparation, also known as "data shaping" or "ETL" (Extract, Transform, Load).

Even if your data looks good, spending a moment here is a good habit. Inside the Power Query Editor, you can:

• Remove unnecessary columns or rows.
• Split columns into multiple columns (e.g., splitting "First Name, Last Name" into two columns).
• Change data types (e.g., ensuring your dates are recognized as dates and your sales figures are recognized as numbers).
• Unpivot data to make it easier to visualize.
• Merge data from two different queries or append one on top of another.

Every step you take is recorded in the "Applied Steps" pane on the right. This means your cleaning process is repeatable. When you refresh your data source later, Power BI will automatically re-apply all those same cleaning steps for you.

Final Thoughts

You've now learned how to connect Power BI to your key data sources, from humble local files to powerful web services. Getting your data into the report is the essential first hurdle, setting the stage for all the analysis and visualization that follows. This foundation enables you to move from just looking at data to truly understanding what it means.

While Power BI is incredibly capable, getting all your data sources connected and tidied up in the Power Query Editor can be repetitive and time-consuming, especially when you're managing dozens of different platforms for marketing and sales. We built Graphed to remove this friction. We provide simple, one-click connectors to all your key tools - like Google Analytics, Shopify, Facebook Ads, and Salesforce - so you can skip the manual setup and jump straight to insights. Instead of learning a complex BI tool, you can simply ask questions in plain English, and Graphed builds real-time, interactive dashboards for you in seconds.