When you can’t merge files in Power BI, it prevents you from creating complete datasets, leading to fragmented information. It stops you from generating insightful data because you cannot effectively establish relationships and dependencies between datasets.
Overall, failing to combine tables from different files limits the app’s capacity to deliver a unified and holistic view of data.
That’s why, in this article, I’ll explain what you need to do when the combine files feature is no longer working in Power BI.
Let’s begin!
How to Fix Combine Files Option Not Working in Power BI
If you are experiencing issues combining files in Power BI, make sure that the files you are trying to merge are compatible and have a consistent structure. Otherwise, Power BI will have problems combining files with varying data types or formats.
Additionally, confirm that the file paths specified in Power BI are accurate and accessible. If the files are located on a network drive, ensure you have the necessary permissions to open them.
Another common issue is related to the Power Query settings. Check the Power Query Editor to ensure the applied transformations and steps align with the data in your files.
If you still encounter problems, check out the methods I’ve described below to troubleshoot the combine files error.
Here are the methods to fix the combine files option not working in Power BI:
1. Expand All Columns with Different Headings
When joining different files in Power BI, combining 2 reports with the same column headings is ideal.
But if the column names vary in the spreadsheets, Power BI only combines the columns of the file you choose as the sample file. The app doesn’t merge the other columns from the spreadsheets.
However, you can easily fix this issue with a single line of code. Here’s the process to expand all columns with different headings:
The Problem
- Launch the Power BI Desktop app.
- Choose the Get data option from the Home tab.
- Select File > Folder and click on Connect.
- Click on Browse and navigate to the folder containing the spreadsheets with multiple column headings.
- Expand the Combine option and select Combine & Transform Data.
- Choose your sample file and the sheets containing the data tables you want to join. Then, click OK.
- Go to the Expanded Table Column1 You’ll see it only expands the columns of the simple file, and other column headings are automatically removed.
The Solution
- Rename the Removed Other Columns1 steps to PreExpand to make the formula more straightforward to write.
- Right-click on the Transform File column and choose Drill Down.
- Click on Continue to create a navigation step.
- Enter the following code to get a list of all the headings in the spreadsheets:
= List.Union (List.Transform( PreExpand[Transform File], each Table.ColumnNames(_)) )
- Rename Transfer File1 to Headings for your convenience.
- Click on Fx, input = PreExpand, and press Enter.
- Click the Expand button, uncheck the Use original column name as prefix option, and click OK.
- Remove everything in the curly brackets, including the brackets.
- Input Headings and press Enter.
Now, all your column headings will be restored.
2. Resolve Merge Queries OK Button Greyed Out
When attempting to combine two queries in Power Query, the OK button will be greyed out as long as your selection doesn’t match all the rows from the first table.
So, choose the same columns from both tables. Then, you’ll be able to click OK and merge queries successfully.
Here are the steps to Merge Queries OK button greyed out:
- Open the Power Query Editor with the queries you want to merge.
- Click the Merge Queries button from the Combine section of the top toolbar.
- Select the columns you want to merge from the 1st table.
- Choose the 2nd table from the dropdown and select the same columns as the 1st table.
- Click OK to merge both queries.
3. Combine Files with Multiple Heading Rows with Power Query
If you combine multiple header rows in a spreadsheet with other files in Power BI, the 1st row will be assigned as the header of the combined file, but the 2nd header row is set in the 1st row of the data.
Also, if you scroll down to the end of the 1st spreadsheet table, the headers will appear again at the start of the 2nd file and repeat throughout the entire dataset for each file.
To solve this issue, you need to fix the layout in the simple query before combining the files.
Here’s how to combine files with multiple heading rows with Power Query:
- Select the Transform Sample File and remove the Promoted Headers step.
- Go to the Transform tab and select Transpose.
- Select the header columns, right-click, and choose Merge Columns.
- Choose a Separator. In this example, I am using Space and click OK.
- Click on Transpose again.
- Click the Table icon and select the Use First Row as Headers option.
- Select the final query from the left panel and remove the Changed Type step.
- Press Ctrl + A to select all the cells and choose Detect Data Type from the Transform tab.
After these steps, all the headings from different spreadsheets will be assigned in the first row of the combined query.
Best Practices for Combining Multiple Excel Files in Power BI
Maintaining a structured and consistent format for your source data is crucial when joining multiple Excel files in Power BI. It ensures seamless integration and enhances the overall data quality. Also, meaningful column names and descriptions further clarify the dataset.
Remember that parameterized queries play a vital role in preventing data duplication when merging files. By using these queries, you can dynamically adjust data retrieval based on specific criteria.
Also, Simplicity is key when building your model with multiple datasets in Power BI.
Emphasizing simplicity does not imply sacrificing functionality. Instead, it promotes a focused and efficient approach to data modeling.
So, try to minimize complex relationships and calculations whenever possible. Also, reduce the dataset size. It’ll increase the report’s responsiveness and user experience.
Final Thoughts
As you can see, by ensuring proper data formatting, verifying file paths, and utilizing suitable Power Query techniques, you can easily overcome the problems you get when combining files (binaries) in Power BI.
Comment below if you have further questions, and we’ll get back to you.
