Combining Tables

[pencekj]

I have a document using cell phone data from multiple carriers (an AT&T table, Verizon table, Sprint table, Bell Canada table and so on). Each table has some of the same columns (phone number, account number, device model...) but also has some columns that are a little different (Verizon has a column called 'Usage Charges' while that data for AT&T would be the sum of 3 different columns on their table). I'm trying to figure out a way to combine all 3 for some pivots without having to copy and paste them into a new table. Copying/pasting isn't ideal because we deal with different data for different customers multiple times/day so this step would be done a lot. Currently our templated document has one giant table off to the side that just does =random cell or =multiple random cells added together and we pivot off that. With as many rows and columns as we have, it is giant and can even crash excel.

Does anyone have any ideas on how to combine tables for a pivot when the tables need to be separate? I can add calculated fields on the individual tables to make the columns all the exact same, unless someone has a better idea.

Thanks so much!

 

[Automatrix]

Make a connection to each of the tables in Power Query. Rename the columns so they match. Then append the queries into a new table, ready for pivoting.

 

[pencekj]

Thanks. I had never used Power Query before but its actually really straightforward and easy to use

 

[Automatrix]

Thanks. I had never used Power Query before but its actually really straightforward and easy to use

It is (sadly) Excel's most well kept secret.