I have two datasets, one has miles driven consolidated under an airport code. So I have a list of airport codes and all the miles associated with that code.
I have a second dataset that is a list of claims and each claim has what airport code the driver works out of, thus I can have many claims from the same airport code. I have a pivot table to group all the claims and how much we paid by the airport code. What I cannot get Excel to do is combine the two datasets into one.
What I want is the ATL miles from the table above, 16,904,451 tacked on the end of the above row from the pivot table. This has to be dynamic as the Miles table and the claims table are updated each month so doing a lookup "outside" the pivot table will not work, plus, I want to get summary information on the miles as well as what we paid.
I have tried linking the tables with the Airport code as a key and adding the miles in the pivot but I think it is taking every row in the pivot and summing the miles and presenting the total for every line.
Was thinking of pulling both root tables the pivot tables pull from into powerquery and relating but again I cannot think of how the consolidate them into one because they both need to be filtered down into the one airport code for them to relate.
Seems like I need one static table that has been pivoted, then the other pivoted data can look up the miles like I have in the example above. Clear as mud right?
Any thoughts here?
| Domicile Airport Code | Miles |
| ABE | 8,501,114 |
| ATL | 16,904,451 |
| AVP | 3,808,334 |
| BDL | 15,222,934 |
I have a second dataset that is a list of claims and each claim has what airport code the driver works out of, thus I can have many claims from the same airport code. I have a pivot table to group all the claims and how much we paid by the airport code. What I cannot get Excel to do is combine the two datasets into one.
| Coverage-Airport | Accident Count | Total Expenses (Paid & Reserves) | Total Indemnity (Paid & Reserves) | Total Incurred |
| ATL | 24 | $51,060 | $1,423,514 | $1,469,724 |
What I want is the ATL miles from the table above, 16,904,451 tacked on the end of the above row from the pivot table. This has to be dynamic as the Miles table and the claims table are updated each month so doing a lookup "outside" the pivot table will not work, plus, I want to get summary information on the miles as well as what we paid.
I have tried linking the tables with the Airport code as a key and adding the miles in the pivot but I think it is taking every row in the pivot and summing the miles and presenting the total for every line.
Was thinking of pulling both root tables the pivot tables pull from into powerquery and relating but again I cannot think of how the consolidate them into one because they both need to be filtered down into the one airport code for them to relate.
Seems like I need one static table that has been pivoted, then the other pivoted data can look up the miles like I have in the example above. Clear as mud right?
Any thoughts here?
on how to avoid "summing the miles and presenting the total for every line":
