Merge multiple tables into one

[FotS]

(I tried posting this in the Stack Exchange forums and it was closed as off-topic, so if this looks familiar to you from somewhere, that's why....)

I have csv reports exported from our company cellphone account detailing data usage for all of our cellphones on a per billing cycle basis. I have a different report for each cycle (trying to run the report for multiple cycles just sums the data usage into one column rather than separate columns for each cycle). I need to merge these into a master report (table) such that no unique rows are lost (from cellphones being added or removed from the account) and (ideally) the data usage column from each report becomes its own column in the master table (if worse comes to worse, I can do the columns bit manually as I already have a formula that works in these. It's the rows that are fully manual for me right now).

Example:

Report 1

Cell number	Name	Device Model	Billing Cycle Date	Data Usage
number_1	Name_A	xxx	Date_1	xxx
number_2	Name_B	xxx	Date_1	xxx
number_4	Name_C	xxx	Date_1	xxx

Report 2

Cell number	Name	Device Model	Billing Cycle Date	Data Usage
number_1	Name_A	xxx	Date_2	xxx
number_2	Name_B	xxx	Date_2	xxx
number_3	Name_D	xxx	Date_2	xxx

Master Report (what I want the merge to look like from the given examples above)

Cell number	Name	Device Model	Data Usage Date_1	Data Usage Date_2
number_1	Name_A	xxx	xxx	xxx
number_2	Name_B	xxx	xxx	xxx
number_3	Name_C	xxx		xxx
number_4	Name_D	xxx	xxx

Ideally, I'd like to be able to do this without macros so I can just leave this as a simple xlsx file. All of the reports are saved to the same workbook on different worksheets as named tables. I'd like the master table to be in the same workbook on its own worksheet and be able to either automatically or easily manually adapt to adding a new (or removing an existing) worksheet with a new named table so I can append it with new (or remove old) reports as time goes on.

I've done some Google searching coming up with results about dynamic & pivot tables (none of these seem to be doing what I want, and pivot tables especially are not formatting anywhere close to what I'd want) and some results about Power Query (this seemed the closest, but when I try this to merge and use the "Full Outer (all rows from both)" option, any rows in table2 that aren't in table1 just get "null" written across the value fields for the entire row).

I'm hoping some better Excel gurus out there can help me with this. I am using the Office 365 version of Excel.

In my previous posting of this question on the Stack Exchange boards, before it was closed, I received a couple of comments:

• One said this was doable via Pivot Tables. I'm not proficient with Pivot Tables. In my attempts to use a Pivot Table for this, I could not get it to actually put the column headers as the actual column headers. I could only put values from a column as the column headers or put everything as a tree structure in the first column. If someone knows more on this, please let me know.
• The other comment told me that Power Query was the right tool, but that I wanted to append, not merge. However, when I tried append, it created duplicate rows of the ones that were common between the tables (so in my example master report above I would've ended up with 6 rows instead of the intended 4). Again, if someone knows more on this, please let me know.

 

[BigBeachBananas]

Try this link. (Using power query and pivot tables).

 

[FotS]

Thank you, but that doesn't do what I want. It results in duplicate lines of data in the master table, and attempting to use it as the data set for the pivot table (as a master table) still does not format the way I want the master table to appear.

 

[FotS]

Ok, I've managed to hack together something that is a combination of Power Query and manual editing. I managed to find something similar on reddit that I was able to kind of adapt. Not the most elegant way of doing it, but it works for now. I'd still like to figure out a better way if possible.

Using Power Query:

1. Add connections for each of my reports (which are formatted as tables)
   1. Click somewhere in the report table
   2. Data tab -> From Table/Range
   3. In the upper left, click the down arrow for Close & Load and select Close & Load to...
   4. In the dialog that opens, select Only Create Connection
   5. Click OK
   6. Rinse & repeat for all of my reports (tables)
2. From the Data tab, select Get Data -> Combine Queries -> Append
3. As I have more than two tables, I select the Three or more tables option and then add all (relevant) tables
4. Click OK
5. In my usage, my reports do have columns I'm not concerned with, so from the Home tab, I selected Choose Columns and deselected the columns I did not need in the master report (I note this here because later, in the manual steps section, this has to be taken into account)
6. From the query editor, I selected the Billing Cycle Date and Data Usage columns
7. Transform tab -> Unpivot Columns
8. I then selected only the Attribute column
9. Transform tab -> Pivot Column
10. In the dialog that opens, set Values Column to Value and expand Advanced options to set Aggregate Value Function to Don't Aggregate
11. Click OK
12. This gets me to where I now only have 1 row per wireless number from all of my reports, but the Billing Cycle Date and Data Usage columns are full of errors ("There were too many elements in the enumeration to complete the operation"). Understandable why, but I don't know how to split these out into their own columns from here. So instead, I remove them:
    1. Home tab -> Choose Columns
    2. Deselect the Billing Cycle Date and Data Usage columns
    3. Click OK
13. Click Close & Load to have it create a new sheet with the completed Power Query table (or choose Close & Load to... to choose where it puts the table)

Now comes the manual parts...:

1. In the new table, add additional columns for each column of data per report that needs to be pulled across (in may case, 1 column each for 5 reports, so 5 columns total)
2. Name them as desired (In my case, "Data Usage 08_2023", "Data Usage 07_2023", etc. For some reason it would not let me use a formula to do this?)
3. In each of the new columns, I used the following formula (where DataUsage_08_2023 is the name of the report table and should be changed in each column for its respective report):

   =IFERROR(NUMBERVALUE(SUBSTITUTE(VLOOKUP([@[Wireless number]], DataUsage_08_2023[[#All],[Wireless number]:[Data usage]], 7, FALSE), "GB", "")), "-- N/A --")

   • VLOOKUP is to pull the correct data usage for each wireless number. The 7 is the column number of the Data Usage column in respect to the selected range (it's so high because of the unneeded columns I deselected during the Power Query)
   • As my report exports all contain "GB" as text in the data usage values and I want a total row, I use SUBSTITUTE to remove the "GB" and NUMBERVALUE to convert the result back into number format
   • As some of the data usage values may not exist on that specific report (due to numbers being added or removed at some point in time), IFERROR is used to assign some value to these (this has to be done so that totaling the column doesn't result in an error). I don't use "0" so that these non-existent ones stand out compared to the actual 0 data usage devices.
4. Finally, on the Table Design tab, put a check in the Total Row option and, for each of the data usage columns, change the total drop down to SUM.

And that gets me the master report I was looking for. Though as I said before, I'd still like to figure out a better way if possible.

(I did notice in Power Query that there was an option for adding custom columns, but the formula section was refusing to take my formulas with table or sheet references in them ("token literal expected" and some other invalid error that I can't recall right now), so that's why I created them manually outside of Power Query.)