Forum Discussion
Two tables, differing data - merge, append new or other?
Hi all,
I have two tables - one that runs for a longer period (AllSales) and one that is shorter, but captures cancelled sales (SalesStatus).
They do not have identical columns, but both have a SalesOwnerID that can be used to pair things up.
My question is how would I pull ALL SalesOwnerID (both historic sales, and those that have been cancelled) so I can review and perform calculations on them?
I thought about appending, but that leaves rows with lots of null values (and as the tables combined are 1m rows plus it's rather data heavy), or carrying out a merge, but if I merge on SalesOwnerID I am still not getting all of the sales through for some reason.
For example:
All Sales has
| SalesOwnerID | Date | Status | Value | ProductID | UniqueID |
| 001 | 12/02/24 | Complete | 40 | 001 | 120224-001-001 |
| 002 | 12/03/24 | Complete | 5 | 001 | 130324-002-001 |
| 001 | 15/03/24 | Dispatched | 20 | 001 | 150324-001-001 |
SalesStatus has:
| SalesOwnerID | Date | Status | ProductID | UniqueID |
| 001 | 13/02/24 | Cancelled | 001 | 130224-001-001 |
| 001 | 10/10/24 | Pending | 001 | 101024-001-001 |
| 001 | 15/03/24 | Dispatched | 001 | 150324-001-001 |
I would like the end result to be simply for SalesOwner 001
| SalesOwnerID | Date | Status | Value | ProductID | UniqueID |
| 001 | 12/02/24 | Complete | 40 | 001 | 120224-001-001 |
| 001 | 13/02/24 | Cancelled | 001 | 130224-001-001 | |
| 001 | 15/03/24 | Dispatched | 20 | 001 | 150324-001-001 |
| 001 | 10/10/24 | Pending | 001 | 101024-001-001 |
If I merge on SalesOwnerID it's not adding in new rows for that ID, and if I append into new I end up with numerous rows with blanks included (I could calculate columns but it seems rather resource heavy for something that should be easier).
Is there a simple trick/method I am overlooking?
let Source = #"All Sales" & #"Sales Status", #"Grouped Rows" = Table.Group(Source, {"SalesOwnerID", "Date", "Status", "ProductID", "UniqueID"}, {{"Value", each List.Sum([Value]), type nullable number}}) in #"Grouped Rows"
8 Replies
Your data is ambiguous as you don't know for sure what the cancellation is for. At a minimum you need to add an order ID column.
Sorry! There is a productid associated too (amongst other things) - I was just trying to get across my data without it being overkill.
Let's just assume that all orders above are for the same product.
based on the sample data you provided, what we can do is to append both tables. I don't not understand why end up with numerous rows with blanks?
maybe you need to update your sample data?
Sorry - again an issue with being too brief with my data sample and corrected in the above now.
Essentially you may get records that appear in BOTH tables, and in that case the append obviously duplicates records (one with, one without value).
The actual data is years of history so I need to keep transformations as light as possible.Still not enough data to disambiguate. You will need to know which order a cancellation is for.
