Combining excel files

[Anonymous]

Hi guys ,

i have a big excel file with many sheets ,i need some guidance to work on it .

First question , is it better to clean the excel file before connection to power BI (i have in one sheet two different tables , the table starts from the sixth row in excel file , is it better to split the sheet to 2 files each one of them contain 1 table ) , 

another question , if power bi combines the files then do the transformations or it is the opposite ( means power Bi combines all the sheets and then do the transformations ) ?

Thank you so much guys  

[aTChris]

Hi

The only easy answer I can give is whichever option gives you have the most flexibility in the long run with the least transformation coding in the short term. If you know the size and format of the tables you have in each sheet won't change, its only the data that will then you can connect and transform the data as they are now. However, if you think in the future it might change you will probably want to save the time now and move them to separate sheets. It's only a copy worksheet and then remove what you don't want from each sheet anyway.

The answer to your second point is you will decide that. You will connect to both datasets, you can then merge immediately and transform. Its as I mentioned earlier, what's the easiest solution for you? If the transformation you will perform is the same to both datasets you don't want to replicate the code. Alternatively, if you need to maintain separation for future flexibility keep it separate then merge the sources towards the end.

[trebgatte]

For Excel, it's best to do a few things to make your life easier. One, use Format as Table so that you only get the table and not the whole sheet. https://support.office.com/en-us/article/create-and-format-tables-e81aa349-b006-4f8a-9806-5af9df0ac6... for the instructions. Secondly, tables should not have merged cells or intermediate headers. Thirdly, a column should have a consistent definition and contain one type of data appropriate for that definition. For example, if it says Ship Date, then hopefully it has actual dates, not "Next Week". 

Using Power Query in the Query Editor, you would create a connection to each table in the Excel workbook. If the tables are the same format and don't need to be pre-cleaned, you can append them to each other. After that, apply whatever transforms you need to the data, like adding custom columns to represent business rule conditions, etc.

Hope this helps!

--Treb

Check out my Power BI blog posts at https://marqueeinsights.com/category/power-bi/ 

[Edwin74]

Hi there,

Let's see if I can help, if only a bit 😉

Question 1: several tables on 1 Excel-sheet doesn't work well with Power BI. I used to end up with only the top table and the other table(s) on the same sheet left out.  Several sheets on the other hand poses no problem for Power BI. To me cleaning up/preparing Excel files was needed before connecting it to Power BI.

Question 2: Power BI doesn't combine sheets on itself. It lets you choose which sheets of a particular Excel-file you want to import. You get to select the sheets you want to import and before you actually import, you get the possibility to alter the layout of the tables. When several tables have been imported to Power BI you also have the opportunity to merge queries.

Hope this is of help.

Cheers,

Edwin