How Do You Get Data into Power BI?

Bringing your data into Power BI is the first and most critical step towards creating beautiful, insightful reports. This guide will walk you through the most common ways to connect your data sources, from simple spreadsheets on your desktop to live data from online services.

Understanding Power BI's Data Connectors

Power BI works by connecting to your data sources, wherever they may live. To do this, it uses a library of hundreds of built-in "data connectors." Think of a connector as a purpose-built gateway that knows exactly how to talk to a specific type of data source, whether it's a file, a database, or a cloud application.

This massive library is one of Power BI's biggest strengths. It means you can centralize data from different places into one unified report. You can pull sales figures from an Excel file, web traffic from Google Analytics, and lead information from Salesforce, and analyze them all together. The connectors handle the technical details of authentication and data retrieval, making the entire process surprisingly straightforward.

The Starting Point: The "Get Data" Window

Every data import journey in Power BI Desktop begins with the "Get Data" button. You'll find it prominently displayed on the Home tab of the ribbon.

When you click this button, a window appears showcasing the most common data sources like Excel workbooks and SQL Server. However, the real power is unlocked by clicking "More..." at the bottom of the list. This opens the full Get Data window, where you can see every available connector neatly organized into categories:

• All: A searchable, alphabetical list of every single connector.
• File: For connecting to local or network files like Excel, CSV, XML, and entire folders.
• Database: For traditional databases like SQL Server, MySQL, PostgreSQL, and Oracle.
• Power Platform: Connectors for other Microsoft services like Power BI datasets and Dataverse.
• Azure: Deep integration with Microsoft's cloud platform, connecting to services like Azure SQL Database and Azure Synapse Analytics.
• Online Services: This is a treasure trove for marketers and business users, containing connectors for Google Analytics, Salesforce, SharePoint, Adobe Analytics, and much more.

You can use the search bar at the top to quickly find the connector you need. For example, typing "Google" will instantly show you the connectors for Google Analytics, Google BigQuery, and Google Sheets.

Method 1: Connecting to Local Files (Excel & CSV)

Connecting to files stored on your computer is often the first thing new Power BI users do. It's perfect for static monthly reports or data exports you've already saved.

Connecting to an Excel Workbook

Excel spreadsheets are one of the most common data sources for businesses. Let’s imagine you have a spreadsheet named Monthly-Sales.xlsx with your sales figures.

1. Click "Get Data" from the Home tab and select Excel Workbook.
2. Navigate to where you saved your Monthly-Sales.xlsx file and click "Open."
3. A new "Navigator" window will pop up. This window shows you all the available data elements inside your workbook. You'll see any worksheets (like Sheet1) and any formally-defined Excel Tables you might have created.
4. Click on a table or sheet name to see a preview of the data on the right side. This helps you confirm you're selecting the right information.
5. Check the box next to each table or sheet you want to import. You can select multiple items from the same workbook.
6. Now, you have two choices at the bottom of the window:

For now, assume your data looks good and click "Load." Power BI will import the data, and you'll see your tables appear in the "Data" pane on the right-hand side, ready to be used in visuals.

Connecting to a CSV or Text File

Comma Separated Value (CSV) files are another popular format for exporting data from various systems. The process is very similar to Excel.

1. Click "Get Data" > "More..." and then select Text/CSV from the list.
2. Browse to your CSV file and open it.
3. Power BI automatically analyzes the file to determine the file origin, the delimiter (usually a comma), and how many rows to use for data type detection.
4. You'll see a preview of the data, correctly separated into columns. If Power BI guessed the delimiter incorrectly (which is rare), you can easily change it from the dropdown menu.
5. Again, you have the option to "Load" or "Transform Data."

Method 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 without having to copy-paste. Let’s try pulling a list of countries by population from Wikipedia.

1. Find the URL of the webpage containing the data you want.
2. In Power BI, click "Get Data" and select Web.
3. Paste the URL into the dialog box and click "OK."
4. Power BI will scan the webpage for any HTML tables it can recognize. The Navigator window will appear, listing all the tables it found.
5. Click through the tables in the list on the left to preview them, just like you did with the Excel file. Find the one that contains the country and population data you need.
6. Check the box next to your desired table and choose "Load" or "Transform Data."

Just like that, you now have live web data in your report. When you refresh the report, Power BI will go back to that URL and pull the latest version of the table!

Method 3: Connecting to SaaS Applications like Google Analytics

This is where Power BI really shines for marketing and sales teams. Instead of manually exporting reports from your favorite SaaS tools every week, you can connect Power BI directly to the source for live, automated reporting.

Let's use a common example: connecting to Google Analytics to visualize your website traffic.

1. Go to "Get Data" > "More..." and search for Google Analytics. Select it and click "Connect."
2. If this is your first time connecting, Power BI will prompt you to sign in to your Google Account. Follow the prompts and grant Power BI permission to access your Google Analytics data.
3. Once connected, the Navigator window will again appear, but this time it will show your Google Analytics accounts.
4. You can drill down by clicking the arrows next to your Account, then your Property, and then your View.
5. When you select a View, a list of available metrics and dimensions appears on the right. This includes things like:
6. Find and check the boxes for the data points you need. For example, you might select 'Date', 'Source / Medium', 'Sessions', and 'Users'. Power BI will combine these into a virtual table.
7. Click "Load" or "Transform Data" to pull your Google Analytics data into your report. Now you can build visuals without ever having to log into the Google Analytics interface again.

The process is similar for other online services like Salesforce, HubSpot, and dozens more, making it easy to create a central hub for all your Key Performance Indicators (KPIs).

"Transform Data": A Quick Introduction to Power Query

We've mentioned the "Transform Data" button a few times. Clicking this opens the Power Query Editor, which is where you prepare your data for analysis.

Raw data is rarely perfect. It might have errors, unnecessary columns, or formatting inconsistencies. Power Query lets you fix all of this before your data gets to the report.

Even if you're a beginner, a few simple transformations can make a huge difference:

• Remove Columns: Got columns you don't need? Right-click the column header and select "Remove."
• Change Data Type: Sometimes numbers are imported as text. You can select a column and use the "Data Type" dropdown in the Home tab to change it to "Whole Number," "Decimal Number," or "Date."
• Filter Rows: Don't want certain data included? Use the filter dropdown arrow on a column header, just like in Excel, to uncheck values you want to exclude.
• Split Columns: You can split a column by a delimiter. For example, splitting a column with "Firstname Lastname" into two separate columns for Firstname and Lastname.

The best part about Power Query is that it records your steps. Every time you refresh your data, it automatically repeats all your cleaning and shaping actions. Set it up once, and your data stays clean forever.

Final Thoughts

Connecting data sources is the foundational skill in Power BI. Whether you’re pulling from a simple Excel file, a live webpage, or a SaaS platform like Google Analytics, the process revolves around the "Get Data" experience and the versatile Power Query Editor for any necessary cleanup. Once you master bringing data in, you unlock the full analytical power of the tool.

If you're managing data from sources like Google Analytics, Shopify, or Salesforce, you know that even with great tools, the setup can take time. At Graphed, we simplify this whole process. We offer one-click integrations for your key marketing and sales platforms and use AI to help you build real-time dashboards with natural language. Just describe what you want to see - "show me leads from Facebook Ads vs. Google Ads this month" - and we generate the dashboard for you, skipping the lengthy setup so you can get to insights faster.