Combine different columns from many Excel Spreadsheets with data into one in Power BI to model.

[Anonymous]

Hi Community,

We are using enablon SYNERGY system (as attached screenshot) to capture Environment, Health & Safety data.

The business wants to report on Health & Safety data using Power BI.

Business is exporting data from enablon SYNERGY system and giving me 4 Excel files because there is a limitation of selecting only 48 fields before exporting in enablon SYNERGY. Therefore, I now have 4 files with 48 Columns in the first 3 Excel files and another 16 in the 4th Excel file as per the subsequent screenshots.

Note: I'm still exploring the possibilities of connecting Power BI directly to enablon SYNERGY system so that I can do data modeling in Power BI, if need be, and report from there.

Question: How should I combine all these 4 Excel spreadsheets into 1 in Power BI (so it looks like the last screenshot) so I can do a Star Schema?

I tried to import all the 4 Excel files from Folder and then Combine & Transform and some other ways but no luck.

Can anyone please help me with how to achieve this if you had been in a similar situation.

Any info with screenshots or guidelines would be of great help to me and others like me.

Hoping to hear soon.

Thanks for your time in advance.

Thanks & regards, B

[JarroVGIT · Accepted solution]

That really depends on what the underlying datamodel is. Can we see this as one big table? Because then you can merge the queries. If you don't have a identifier column (ID for example) in all sheets in which the merge can determine what data belongs together, please consider loading all for excel sheets seperately, then add an Index column and merge the queries.

For your convience, I've created two small tables, let's assume these are results of importing two different excel sheets. I want to combine these tables so I have colunns ID, Column1 and Column2 in one 'big' table. 

Table 1Table 2

These examples already have an identifier column (ID) on which I can perform the merge on. If you don't have that, please add an Index column on all tables in Query Editor -> Add Column tab -> Index Column.

Now I am going to merge these two queries. I select query1 (table1) and go to Merge. Choose 'Merge Queries as New'

Select Table2 in the dropdown menu, and select both ID columns in the preview bits. Join kind should be Left Outer.

Now, Press OK and as final step we are going to select which columns you want to merge together in the new table from Table2:

Deselect ID and only leave Column2 selected.

Press OK and look at your new merged table.

Resulting merged Table.

Repeat this step for every subsequent query you need to merge.

That should work. 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[MKDK]

Hi,

did you find any better way to extract data from Enablon? If yes, could you share any experiences?

[JarroVGIT · Accepted solution]

That really depends on what the underlying datamodel is. Can we see this as one big table? Because then you can merge the queries. If you don't have a identifier column (ID for example) in all sheets in which the merge can determine what data belongs together, please consider loading all for excel sheets seperately, then add an Index column and merge the queries.

For your convience, I've created two small tables, let's assume these are results of importing two different excel sheets. I want to combine these tables so I have colunns ID, Column1 and Column2 in one 'big' table. 

Table 1Table 2

These examples already have an identifier column (ID) on which I can perform the merge on. If you don't have that, please add an Index column on all tables in Query Editor -> Add Column tab -> Index Column.

Now I am going to merge these two queries. I select query1 (table1) and go to Merge. Choose 'Merge Queries as New'

Select Table2 in the dropdown menu, and select both ID columns in the preview bits. Join kind should be Left Outer.

Now, Press OK and as final step we are going to select which columns you want to merge together in the new table from Table2:

Deselect ID and only leave Column2 selected.

Press OK and look at your new merged table.

Resulting merged Table.

Repeat this step for every subsequent query you need to merge.

That should work. 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[Anonymous · Accepted solution]

Hi @JarroVGIT 

Thanks a million for your time and effort. This works perfectly fine.

Kind regards, B

[JarroVGIT]

You are very welcome! (Ia think you marked the wrong post as the solution by accident:) )