The code should looks like this now Sheet index zero represents the very first sheet in the Excel file Now use the Sheet index inside the instead of anything written there. This step, sometimes might be called other things and you might need to check the 2nd or the 3rd step after the Source to find it. Enable the formula bar in the View tab if you cannot see it in the editor. Note that the above query can be found in Power Query Editor, which is accessible using “Transform Data” in the Power BI Desktop.Īfter finding the query, go and find the Navigation step, with selecting this step, you will see the formula bar’s expression. This should be under a folder and named something like “Transform Sample File”. Now that you know the sheet index, you can fix the function that is automatically created by the Power Query when you combined files. for example in the scenario below, that order can be like this (start from zero on the left side).
The first solution to use for the problem above is that you can use the index of the sheet, which means the order of the sheet among other sheets.
Sheet Name asked for the Sample Fileīecause in my files, this sheet name only exists in one file, it means the combined result would only work for one of the file, and not the others! the process works only for the file that has the same sheet name Solution 1: Use Sheet Index Not the Name The Combine process, asks one of the files to be a template, and from that template, I need to choose the Sheet Name that contains the data which I want to be combined across all files. If you use the “Get Data from Folder” option in Power BI Desktop and Combine the Files, you would get the below results: Combining Files
I have a set of Excel files like below A set of Excel filesĮach file has the same structure of the data, but their sheet name is different Each file has a different sheet name Problem with Get Data from Folder In this blog article, I’ll explain a method you can use that works regardless of the sheet names. However, if your sheet names are different in the list of Excel files, then you will face a problem. The ability to use Get Data from Folder in Power BI is a very useful option.