Combine data from 3 different spreadsheets

[Johnweet]

I've seen some posts that nearly address my issue but not quite.  I receive 3 reports each month.  these come from 3 different finance teams that operate with 3 different systems.  This means the column names are different and they are in a different order.  There are several columns that contain the same data and I want to prepare reports based on these columns.  The reports are to do with the money we spend with vendors.  Each report contains columns for date, vendor name, amount, and spend category.

I have placed each of these files in a Sharepoint folder.  I then link to this folder, find my reports and transform the data so that the column headers I'm interested in are the same.  I now want to prepare reports that combine all 3 sets of data into one.

How do I do that?

I am a newbie so would appreciate a nice simple explanation.

Many thanks

[S_JB]

Do you get a blank result when you union the table giving you the issue with the others separately?:

Combined Table Test 1 = UNION(APAC,Americas)

Combined Table Test 2 = UNION(EMEA,Americas)

[Johnweet]

All fixed now and working perfectly.  I just started again and used a different table as my starting point.  I moved the columns around in the other tables and it worked. Not sure what the issue was.

[S_JB]

Glad this has worked for you!

[Johnweet]

A case of 2 steps forward 1 step back.  Why I couldn't see the data in the Americas table was because there were thousands of blank rows.  I deleted them and all seems OK. The issue is now as follows.

In order to create the union, I use data transform to delete some columns and move others about. When I look at the columns in transform I have the same number of columns in the same order in each table.  When I perform the union it seems to forget the order that I put the columns in and I get a mix of data in each column.  If I can just fix this I am all good.  Any further suggestions?

[Johnweet]

Hi.  Yes I do.  If I union the Americas with either of the other tables I get a blank, but union them together it's fine.  The issue appears to be with the Americas table.  When I look at the table in transform I see all the data.  When I save that and switch to table view I see nothing.

I've been to my excel file and checked the column formats, all seems to be OK.  I get the files delivered every month.  I went back to a previous file and that is still the same.  The spreadsheet is connected to external data sources (but then so are the others) so I disconnected it from those data sources.  Always the same. I see the data in the spreadsheet. I see the data in transform view, I don't see the data in the table view.   

[S_JB · Accepted solution]

You can combine the 3 tables by using a union. Go to the modelling tab, select new table and enter the logic below (the table names will need updating to what you have called them):

Combined Table = UNION(Table 1,Table 2,Table 3)

Please use the link below to learn more about the UNION function:

https://docs.microsoft.com/en-us/dax/union-function-dax 

[Johnweet]

I think I'm getting there.  I've used transform data to get all spreadsheets the same by renaming columns, moving them, and deleting those that don't match.  All good so far.  See below.

Then I go to the modelling tab and add the logic.  All seems fine but the table is empty.  I have traced it to just one table.  If I delete that one from the logic the other two combine OK.  All the columns are the same type in all tables.  See below.

Any ideas?

Thanks

[Johnweet]

Thanks for the suggestion. That doesn't work because the tables have a different number of columns in a different order. I need to be able to say something like combine these tables so that column 1 in table 1 = column 3 in table 2 and column 5 in table 3 etc.