How to Import Excel Files from ZIP Archives into Power BI
Question details
The user needs to connect to and extract data from Excel files that are compressed inside ZIP archives directly within Power BI or Power Query.
- Product
- Power BI / Power Query
- Device & OS
- not provided
- Scenario
- Connecting Power Query to a SharePoint folder or local directory to combine tables from multiple Excel files stored inside ZIP archives.
- Observed behavior
- Power Query connects to the folder and retrieves the ZIP files as binaries but cannot natively expand their contents without specialized ZIP extraction logic.
Ensure you have the exact SharePoint URL or local folder path containing your ZIP archives, and verify that the internal Excel files share a consistent table structure for appending.
Use Custom Power Query Extraction Logic
This is the standard approach for handling ZIP binaries directly inside Power Query by invoking a custom M function to enumerate and extract the internal Excel files.
Power Query does not feature a native one-click unzip button. Instead, you must load the ZIP files as binary data and apply custom M code to decompress the archives on the fly.
Note that processing large or numerous ZIP files dynamically during data refresh can be resource-intensive and may increase load times.
Open Power BI Desktop, click on 'Get Data', select 'SharePoint folder', and enter your site URL. Click 'Transform Data' to load the file list into the Power Query Editor.
Click the filter dropdown on the 'Extension' column and select '.zip' to ensure you are only processing your compressed archives.
Create a new Blank Query and paste a trusted custom M function for ZIP extraction (often provided in Power BI community forums). In your main query, go to 'Add Column' > 'Invoke Custom Function' to decompress the 'Content' binary column.
Once the function extracts the internal files, filter the new column for '.xlsx' files. Click the double-arrow expand icon to unpack the tables, then append files with matching structures.
Pre-extract Archives using Power Automate
For large datasets, automating the extraction process before data reaches Power BI drastically improves data refresh speeds and avoids memory bottlenecks.
Prepare Your Excel Datasets Seamlessly with WPS Office
While Power BI and Power Query are essential for advanced data modeling, preparing your raw Excel data before importing is equally crucial. WPS Office offers a free, lightweight spreadsheet editor that handles heavy Excel datasets with ease, providing seamless compatibility with Microsoft formats.
- 1. Download and Install: Download WPS Office from the official website and install it on your device in seconds.
- 2. Open Your Datasets: Double-click your raw Excel or CSV files to open them directly in WPS Spreadsheet for preliminary review and formatting.
- 3. Clean and Save: Remove blank rows, standardize naming conventions, and save as a standard .xlsx file before archiving it for Power BI ingestion.
Frequently Asked Questions
Can Power Query natively unzip files without custom code?
No, Power Query does not currently feature a native one-click extraction tool for ZIP files. You must use a custom M function or external tools to parse and decompress ZIP binaries.
How do I handle password-protected ZIP files in Power BI?
Power Query cannot decrypt or decompress password-protected ZIP files natively. You must remove the password protection or extract the files using a secure script or third-party tool before importing the data into Power BI.
Why does my data refresh take so long when using ZIP files?
Decompressing archives on the fly consumes significant CPU and memory resources during the Power Query execution phase. For large data volumes, it is highly recommended to pre-extract the files using Power Automate or a batch script.
What if my ZIP archive contains multiple different file types?
After expanding the initial ZIP binary using your custom function, you will see a list of the extracted files. You can simply filter the 'Extension' column to include only '.xlsx' or '.csv' before expanding the data tables.
