Power BI is a business intelligence and analytics service by Microsoft that allows users to visualize data through interactive reports and dashboards. A key functionality of Power BI is its ability to connect to various data sources and import data for analysis and reporting.
What types of data can you import into Power BI?
Power BI supports importing data from a wide variety of sources, both cloud-based and on-premises. Here are some of the most common types of data sources that can be imported into Power BI:
- Excel spreadsheets
- CSV files
- SQL Server databases
- Azure SQL Database
- Azure SQL Data Warehouse
- Oracle databases
- MySQL databases
- PostgreSQL databases
- SAP HANA
- Salesforce objects
- Azure Blob storage
- Azure Table storage
- JSON files
- ODBC connections
- Spark on Azure HDInsight
- Amazon Redshift
- IBM DB2
- Teradata
- MongoDB
- Cassandra
- Web data (via Web connector)
As you can see, Power BI supports an extensive list of data sources. Basically, if your data is stored in any major database, cloud service, or file format, there is likely a way to import it into Power BI for analysis.
How does Power BI import data?
Power BI uses connection strings and data connectors to import data from external sources. Here is a quick overview of how the import process works:
- Establish a connection to the data source using a connection string or data connector. Many common sources have native connectors built into Power BI.
- When prompted, authenticate and provide any credentials required to access the data source.
- Optionally select a specific database, schema, and table within the data source to import.
- Choose import settings like detecting data types and handling errors.
- Preview the data to ensure it looks accurate.
- Load the data into Power BI.
Behind the scenes, Power BI will translate the external data into optimized internal data structures so it can be used for reporting. The original data source is not modified during this process.
Import methods in Power BI
There are three main ways you can import data into Power BI Desktop and the Power BI service:
1. Get data
The Get data experience allows you to pick a data source, establish a connection, select specific data to import, and load it into Power BI. Get data supports connections to files, databases, and web sources. This is the most common way to import data into Power BI.
2. DirectQuery
With DirectQuery, data is queried live at the source when a report is generated rather than imported. This allows reports to always show the latest data. Not all data sources support being used live via DirectQuery.
3. Push data
For some data sources like SQL Server, Power BI can establish a scheduled push data connection. With this connection type, the external source will automatically push updated data into Power BI on a recurring schedule.
Import options
When importing data using Get Data, there are some additional options you can specify:
Load to / Load to only
You can choose to load the imported data into the Power BI model or just query the data source without loading it. Loading brings data into memory for increased report performance.
Detect data type
Power BI will scan the data and assign data types like text, date, numeric, etc. automatically. You can also specify data types manually if needed.
Credentails / Privacy levels
Authentication options and data privacy levels can be set when applicable. For example, you can import data anonymously or specify Windows/database credentials.
Import best practices
Follow these best practices when importing data into Power BI:
- Import data at the highest level of detail possible, even if you aggregate later.
- Choose a connection method that can refresh frequently to avoid stale reports.
- Ensure adequate privacy levels and credential security.
- Watch for input data errors and clean as needed before importing.
- Load to memory when possible for faster performance.
- Minimize queries back to the source to reduce load.
Benefits of Power BI data imports
Importing data into Power BI provides several benefits:
- Allows use of data from virtually any source for unified reporting.
- Enables fast report interactivity due to in-memory storage.
- Provides data modeling and data mashup capabilities.
- Allows scheduled refreshes from many sources to keep data current.
- Simplifies access to data compared to learning multiple sources.
- Scales to large data volumes.
- Can track incremental data changes and updates.
Limitations of Power BI data import
There are a few limitations to be aware of with Power BI data imports:
- Data upload size limits exist, especially in the cloud.
- Data needs to be reimported/refreshed to see latest changes.
- Limited transformation ability compared to ETL tools.
- Cannot modify data at the original source.
Steps to import data from Excel
Follow these steps to import data from an Excel file:
- Launch Power BI Desktop.
- Select Get Data from the Home ribbon.
- Select Excel as the data source.
- Browse to and select the Excel file to import.
- In the Navigator dialog, select the specific sheets/tables to import.
- Optionally, click Edit to modify import settings like data types.
- Click Load to import the data.
The Excel data will now be imported into the Power BI model for use in reports.
Steps to import data from a database
Here are the steps to import data from a SQL Server database:
- Open Power BI Desktop.
- Choose Get Data > SQL Server database.
- Enter your SQL Server connection string or server and database names.
- Select Database authentication and enter your username and password.
- Choose the database to connect to and select Connect.
- In the Navigator, check the tables/views to import and click Load.
The database tables will now import into Power BI. You can repeat these steps for any database type supported by Power BI.
Incremental refresh
Incremental refresh allows Power BI to only load the newest or updated data from a source. This avoids long data refresh times when importing large datasets. Follow these steps to enable incremental refresh:
- In Power BI Desktop, open query settings for a source.
- Enable the option for Only retrieve data starting from:
- Set the start date for new data.
- In Power BI service, schedule refresh configuration.
- Check the Incremental refresh setting.
Now Power BI will only import new/modified data on refresh rather than the entire dataset. Incremental refresh works best when a timestamp or index column can track new data.
Troubleshooting import issues
Here are some common import problems and solutions:
No data appears after import
Verify the correct object is selected from the Navigator. Check preview rows before loading.
Data import is slow
Limit columns imported. Filter rows if possible. Check network connection.
Error establishing connection
Confirm connection string/server details are correct. Check credentials and permissions. Validate source is available.
Not pulling latest data
Enable incremental refresh. Schedule frequent refresh. Use DirectQuery if supported.
Data format errors
Adjust date/decimal separators on import. Clean malformed data at source. Specify data types.
Conclusion
Importing data is a core function of Power BI that enables building reports over variety data sources. Power BI can pull data from nearly any database, file type, or web source. Best practices exist to optimize and troubleshoot the import process. Overall, data import provides the foundation for impactful business intelligence solutions using Power BI.