Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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:
Press OK and look at your new 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! 🙂
Proud to be a Super User!
Hi,
did you find any better way to extract data from Enablon? If yes, could you share any experiences?
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.
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.
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:
Press OK and look at your new 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! 🙂
Proud to be a Super User!
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |