Two tables, differing data - merge, append new or other?

[twofingertyper]

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?

[ryan_mayu]

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?

[twofingertyper]

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. 

[lbendlin]

Still not enough data to disambiguate. You will need to know which order a cancellation is for.

[twofingertyper]

Then we can add in a uniqueID from the fields present, due to the nature of the sales someone cannot have a successful and cancelled order in the same day - so there will only be one status per day and any totals for a day (where there's a matching salesowner and productID) can be rolled up into one. 

My issue is that I currently try to append the two tables and get a result that produces duplicate lines as opposed to merging everything into a single row.  

[lbendlin · Accepted solution]

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"

[twofingertyper]

Thanks - that works, I did wonder about the impact of grouping larger data chunks, but this will work for now I think. 

[lbendlin]

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.

[twofingertyper]

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.