Reza is also co-founder and co-organizer of Difinity conference in New Zealand. Reza is an active blogger and co-founder of RADACAD. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. He has a BSc in Computer engineering he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant.
Download Sample Power BI Fileĭownload the sample Power BI report here: You can also use any other combinations of text filters, such as Contains, ends with etc. Now in the result set, it is very likely that you have one sheet only, which can be accessed using the sheet index zero Using the sheet index zero Insert the new step, and then enter the characters you expect in all sheet names The first few characters that are repeated in all the sheet names To filter the sheet names based on a template you can use this approach: Filtering based on the sheet name starts with something Then if you filter it for that name, then it is very likely that the only sheet with that name template would be in the result set which is then accessible with sheet index 0. For example, try to find a name template. If the sheet you are looking for is not always at the same position (index) in all the files, then find other alternatives. Remove steps that are hard-coding column names Solution 2: Filter to the Name Template
You need to scan the list of steps, and remove any step that has something hardcoded with the column names in it, for example remove Changed Type steps. Sometimes, there is a little difference in the content of each Excel file, and you might have a column or two that do not exists in other files. so make sure to filter the Kind field in the Source tab to only Sheets (or anything else you are looking for) Filter to have only Sheets Caution 2: Remove Unnecessary Steps The method above might not work if you have other items in addition to the sheet, such as a table or anything else. This action should also automatically fix the function associated with that query, and as a result your combined result should be all working without any errors Combined data is fixed now Caution 1: Remove Unnecessary Items
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.