Creating/merging a table so that it shows data from multiple tables

[Anonymous]

I have four excel sheets that contain a lot of similar but different columsn names as well on four different task types (Incidents, Request, Problems and Changes). I have seen it done but cant figure out myself how to create an ‘All cases’ table that sort of links them together so I can create graphs and visuals where I will be able to show multiple task types on one graph or choosing to filter out some using the filter options etc. I have tried this using ‘Merge/Append’ queries when loading in the data but cant quite seem to understand what to do. I have tried loading the data in separately and then trying to manage a relationship between them but I don’t think that’s right either. There is a task type column in each excel which defines which task each thing is so don’t know if that means I could use that as a point of reference. I think I need to merge/append them somehow into one table then link the tables together but getting stuck, any ideas? Thank you

[Mariusz]

Hi @Anonymous 

Can you create a data smaples for the tables and one for the expected outcome?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Thank you for responding so quick, I have put the four excels into 4 tabs of one excel sheet but unsure if I can attach here, I will look to find you on LinkedIn to send an email with the document. Ideally need to load this data into Power BI so I guess I would have 4 tables, one for each task type that I would hide and they would all link together into one 'All Cases' table that I would use for my graphs and visuals. As they share some columns but not all, I assume the 'All Cases' would need to have every unique column name right?

[Mariusz]

Hi @Anonymous 

If you looking to Append these tables then having the same names is crucial so the data is in the same column, otherwise, it would be a separate column.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

As you can see I have copied in the four excels, using just a few lines to keep it simple. When I want to load this data into Power BI are we saying that to create a table containing all the four tables, there would need to be a column for each unique name (i.e if excel one has Name, caller, task type and excel two has name, task type, group.....I would need to create a column for caller and group when I append/merge?)

Excel 1

Number	Company	Caller	Opened	Task Type
INC08279	A	Adam	2020-06-30 22:08:59	Incident
INC08279	A	Jay	2020-06-30 22:08:57	Incident
INC08279	A	Adam	2020-06-30 22:08:57	Incident

Excel 2

Company	Number	Short description	State	Opened	Close code	Created	Task type	Caller
A	CHG00372	Test	Canceled	2020-06-16 11:06:36		2020-06-16 11:09:33	Change Request	Bill
A	CHG00372	Test	Authorize	2020-06-05 11:25:07		2020-06-05 11:32:18	Change Request	Eng
A	CHG00371	Test	New	2020-06-02 16:49:18		2020-06-02 16:56:11	Change Request	Rob
A	CHG00371	Test	Assess	2020-06-01 13:45:18		2020-06-01 13:45:19	Change Request	Tim
A	CHG00371	Test	Closed	2020-05-26 09:06:41	Successful	2020-05-26 09:06:41	Change Request	John

Excel 3

Company	Number	Opened	Task type	Assignment group	Configuration item	Related Incidents	Priority
A	PRB004322	###########	Problem	Client Feedback		0	4 - Low
A	PRB004299	###########	Problem	Problem Management		0	4 - Low
A	PRB004290	###########	Problem	Problem Management		1	3 - Moderate

Excel 4

Number	Item	State	Company	Task Type	Assigned to
RITM02166	Make A General Request	Fulfilled	A	Request	engineer
RITM02166	Make A General Request	Fulfilled	A	Request	engineer

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

Are there any relationships between the four tables?

If they are parts of one table, you could use "Append Queries" feature.

[Anonymous]

Well I have to get the tables seperatly but as you can see, they do share a lot of similar column headers but obviously there will be some that will have different ones (i.e some columns from the change table will not feature in the incident table) but I guess thats ok as in the example you have given above, we would just append these and no data would be there but thats fine as I wouldnt expect it to be and the visuals/graphs I want to create wouldnt require it. I will try this out and let you know if it works

Thank you

[Mariusz]

Hi @Anonymous 

You can copy from Excel and paste in the body of this post.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn