Introduction
Successful Power BI reporting starts with reliable, well-prepared data. Regardless of how attractive a report looks, inaccurate, incomplete, or inconsistent data can lead to misleading insights and poor decisions.
In real-world projects, data often comes from different sources, including:
- Excel files
- CSV files
- SQL Server databases
- JSON files and APIs
- PDF documents
- SharePoint folders
Power BI makes it possible to connect to these sources and prepare their data using Get Data and Power Query.
In this guide, you will learn how to connect Power BI to different data sources, preview the available data, assess its quality, and prepare it for analysis and modeling.
High-Level Overview of Power BI Data Architecture
Power BI acts as a central environment for bringing data from different sources together and preparing it for analysis.
The main components include:
- Power BI Desktop → Used for data preparation, modeling, and report development.
- Data Sources → Excel, CSV, SQL Server, JSON, PDF, SharePoint, and other supported sources.
- Power Query Editor → Used to connect, inspect, clean, transform, and profile data.
- Data Model → Stores the prepared data for analysis, calculations, and visualizations.
The general flow is:
Data Sources → Power Query → Data Model → Reports & Visualizations
What You’ll Accomplish in This Guide
By completing this guide, you will learn how to:
- Open and configure Power BI Desktop.
- Connect to Excel, CSV, SQL Server, JSON, PDF, and SharePoint data.
- Preview and examine source data using Power Query.
- Use Column Quality, Column Distribution, and Column Profile.
- Identify common data quality issues.
- Prepare data for modeling and reporting.
Getting Started with Power BI Desktop
To follow the practical exercises, download the practice files using the link below:
After downloading:
- Extract the ZIP file.
- Locate the extracted files.
- Open 01-Starter-Sales Analysis.pbix in Power BI Desktop.
The starter file has automatic relationship detection disabled, allowing you to focus on data connection, ingestion, and profiling before moving into data modeling.
Connecting Power BI to SQL Server
SQL Server is widely used by organizations to store and manage structured business data. Power BI provides a direct connection to SQL Server, making it possible to retrieve data for analysis, transformation, and reporting.
Steps to Connect
- In Power BI Desktop, go to Home → Get Data → SQL Server.
-
In the SQL Server database connection window, enter:
-
Server:
localhost - Database: Leave this field blank.
-
Server:
- Select Windows Authentication.
- Choose Use my current credentials under the Windows authentication option.
- Select Connect.
If Power BI displays a warning indicating that an encrypted connection cannot be established, select OK to continue.
- In the Navigator pane, expand the AdventureWorksDW2020 database.
-
Select the following tables:
DimEmployeeDimEmployeeSalesTerritoryDimProductDimResellerDimSalesTerritoryFactResellerSales
- Select Transform Data to open the Power Query Editor.
The Power Query Editor will open with the six selected queries available for further inspection, profiling, and transformation. At this stage, the data has been connected to Power BI, but you can review and prepare it before loading it into the data model.
Previewing and Examining Data in Power Query Editor
Power Query provides a practical environment for inspecting and understanding data before it is loaded into the Power BI data model. Reviewing the data at this stage helps you identify its structure, quality, and potential issues early.
Queries Pane
Each imported table is displayed as a separate query in the Queries pane on the left side of Power Query Editor. Selecting a query displays a preview of its columns and records.
Dimension Tables
Dimension tables generally contain descriptive information used to categorize and analyze business data. In this example:
- DimEmployee → Contains employee information, typically with one row representing each employee.
- DimProduct → Contains information about individual products.
- DimReseller → Contains information about resellers and their businesses.
- DimSalesTerritory → Provides geographical information, including regions, countries, and territory groups.
Fact Tables
Fact tables contain transactional or measurable business data. In this example:
- FactResellerSales → Contains sales transaction records, with each row representing a sales order line.
Understanding how fact and dimension tables differ is fundamental to designing effective star-schema models in Power BI.
Assessing Data Quality with Power Query
Power Query includes built-in profiling features that help you evaluate the quality and structure of your data before using it for analysis.
Column Quality
To display column quality information:
View → Column Quality
This feature provides an overview of:
- Valid values
- Empty or null values
- Errors
For example, the Position column in DimEmployee contains a significant proportion of empty values. This may indicate a data-quality issue that should be investigated before the data is used for reporting.
Column Distribution
To examine the distribution of values:
View → Column Distribution
This feature helps you identify:
- The number of distinct values
- The number of unique values
For example, if EmployeeKey has the same number of distinct and unique values, it indicates that each value appears only once. This can be useful when evaluating potential key columns.
Column Profile
To examine detailed information about a specific column:
View → Column Profile
Select a column, such as BusinessType in DimReseller, to inspect its values and distribution.
For example, you may identify inconsistent entries such as:
WarehouseWare House
Such inconsistencies can cause the same business category to appear as separate groups during analysis. They should therefore be reviewed and standardized during the data transformation process.
Importing Data from Text and CSV Files
Text and CSV files are common sources of business data, particularly when information is exchanged between systems or maintained in simple tabular formats.
Importing a CSV File
Step 1: In Power BI Desktop, select:
Home → Get Data → Text/CSV
Step 2: Browse to and select ResellerSalesTargets.csv.
The file contains:
- One record for each salesperson and year
- Monthly sales target information
- Hyphens used in place of null values
Repeat the same process to import ColorFormats.csv, which contains values used for color formatting.
After importing the files, review their contents in Power Query to confirm that the columns, values, and data types are appropriate before proceeding with further transformations or modeling.
Importing Data from Excel Files
Excel remains one of the most commonly used tools for storing and managing business data.
Importing an Excel Workbook
Step 1: Go to Home → Get Data → Excel.
Step 2: Browse to and select the required Excel workbook.
Step 3: Select Transform Data to open the dataset in Power Query for further preparation.
Excel is particularly useful for:
- Budget and financial worksheets
- Manually maintained business records
- Operational trackers and activity logs
Importing Data from JSON Files
JSON is a common data format used by APIs, web applications, and modern software systems.
Importing a JSON File
Step 1: Go to Home → Get Data → JSON.
Step 2: Select the JSON file or exported API response.
Step 3: Allow Power Query to interpret and expand nested data structures.
Step 4: Flatten and transform the fields according to your reporting requirements.
Because JSON can contain nested objects and arrays, additional transformation is often required before the data can be modeled effectively.
Importing Data from PDF Files
Power BI can identify and extract tabular data from supported PDF documents.
Importing a PDF
Step 1: Go to Home → Get Data → PDF.
Step 2: Select the required PDF document.
Step 3: Review the tables identified by Power BI.
Step 4: Select the required tables and choose Transform Data to continue working in Power Query.
PDF extraction can be useful when working with:
- Financial statements
- Bank and transaction reports
- Compliance and regulatory documentation
Importing Data from SharePoint Folders
SharePoint provides organizations with a centralized environment for storing and collaborating on files.
Connecting to a SharePoint Folder
Step 1: Go to Home → Get Data → SharePoint Folder.
Step 2: Enter the SharePoint site URL and complete the required authentication.
Step 3: Filter the available files and combine the required datasets.
This method is especially useful when reports depend on multiple files stored in a shared SharePoint location.
Why Data Profiling Is Important
Data profiling should be performed before creating relationships, measures, and dashboards. It helps you:
- Identify missing or incomplete values
- Detect inconsistent categories and labels
- Validate columns used for relationships
- Examine value distributions and patterns
Ignoring these checks can result in:
- Incorrect or broken relationships
- Inaccurate KPIs
- Misleading analytical results
Power Query provides an effective environment for validating and preparing data so that it is accurate, consistent, reliable, and ready for analysis.
Conclusion
Connecting to multiple data sources is a fundamental skill for Power BI data analysts. Power BI simplifies this process through its broad range of connectors and Power Query capabilities for inspecting, transforming, and preparing data before it enters the model.
By combining information from sources such as SQL Server, Excel, CSV, JSON, PDF, and SharePoint, you can develop comprehensive and scalable reports that support reliable business analysis.
Mastering data connectivity and preparation provides a strong foundation for building Power BI solutions that are not only visually effective but also accurate, trustworthy, and valuable for decision-making.
Top comments (0)