Power Query helps business analysts and data professionals bring external files into Power BI and Excel quickly. Understanding which file formats can be imported using Power Query reduces preparation time and avoids format related errors.
Many teams rely on this feature to centralize reporting pipelines, especially when source systems deliver spreadsheets, databases, or structured text exports on a regular schedule.
| Format Category | Specific File Format | Typical Use Case | Native Power Query Support | Connector Notes |
|---|---|---|---|---|
| Spreadsheet | .xlsx, .xls | Department reports, finance templates | Yes | Select sheet or named range, preserve types |
| Database | SQL Server, Azure SQL, Oracle | Operational data warehouse queries | Yes | Direct query or import, credential management |
| Text Delimited | .csv, .txt, .tsv | Logs, export files from legacy systems | Yes | Delimiter and encoding configuration required |
| JSON and XML | .json, .xml | API responses, nested hierarchical records | Yes, with transformation steps | May require table promotion and schema flattening |
| Cloud and Web | REST API, OData, SharePoint | SaaS platforms, modern content stores | Yes, via built in connectors | Requires endpoint URL and authentication |
Supported File Formats in Power Query
Power Query includes a robust set of file format handlers that let analysts load data without writing complex code. These native connectors read the structure, apply basic type detection, and return clean tables ready for modeling. The majority of day to day import scenarios are covered out of the box.
Spreadsheet Formats and Compatibility
Excel and CSV Compatibility Details
The spreadsheet group represents one of the most common sources for Power Query imports. Analysts routinely connect to .xlsx workbooks that contain multiple worksheets, formatted tables, and named ranges.
.csv and .tsv files are handled through the text connector, where you define delimiter, encoding, and whether the first row contains headers. Power Query preserves column names, applies basic type detection, and allows easy replacement of problematic rows.
Database and API Connectivity
Direct Query Options for Structured Sources
For relational systems like SQL Server, Oracle, and Azure SQL, Power Query opens a direct query or import path. You supply server name, database, and credentials, then select tables or write custom SQL for precision.
Web connectors handle REST API and OData sources, enabling JSON parsing and parameter driven requests. This makes it simple to refresh dashboards that depend on cloud services or microservice backends.
Text, JSON, and Hierarchical Sources
Working with JSON, XML, and Log Files
JSON files often require a few additional steps, such as promoting headers and expanding nested records. Power Query transforms these structures into flat tables that join easily with other data models.
XML follows a similar pattern, with Power Query providing tools to extract nodes and attributes. When log files use consistent delimiters and timestamps, they become excellent candidates for automated refresh schedules.
Performance and Governance Considerations
Large binary formats like Excel may load more slowly than raw CSV files, so volume and refresh frequency influence format choice. Using Power Query data gateways with on premises sources ensures secure authentication and stable pipelines.
Standardizing on a small set of formats across teams improves maintainability and reduces errors during collaborative development of analytical solutions.
Recommended Practices for Importing Data
- Prefer standardized formats like CSV for high volume logs to reduce processing overhead.
- Use Excel only when formatting or formulas are required for pre processing steps.
- Leverage native database connectors for transactional systems to maintain performance and auditability.
- Validate JSON and XML schemas before building reports to avoid runtime errors during refresh.
- Secure credentials with gateways and Azure Key Vault for cloud based sources like REST APIs and SharePoint.
FAQ
Reader questions
Can I import password protected Excel files using Power Query?
Yes, you can connect to password protected Excel workbooks by supplying the password in the import dialog, and Power Query will decrypt the file before loading its tables.
Does Power Query handle corrupted CSV files gracefully?
Power Query includes error handling options such as disabling row skipping and adjusting buffer size, which helps when loading CSV files that contain malformed rows or mixed delimiters.
Are there size limits for importing JSON files through Power Query?
Power Query can process large JSON files, but memory and refresh time depend on available system resources and how deeply nested the structure is.
Can I schedule refreshes for SharePoint or REST API based imports?
Yes, after publishing the report to the Power BI service or using Data Flows in Power Platform, you can schedule automatic refreshes for these API and SharePoint connections.