Getting stuck with "duplicates" appending two sheets in Power Query

Question

I'm trying to append two sheets in power query, but both sheets have different data(sheet 1 clicks and sheet 2 sends and delivered).
Sheet 2 has data sometimes not found in sheet 1 so that just adds another of the same column.
Is there a way to "combine" the rows, specifically, Main ID 7500.
There more data and sheets, so automation is always preferred.

Should be:

1/18/2024	7500	5053	2242089	1885816	0	4242	25

Answer 1

If you want the data to be on a single line without merging, you can append the tables and then GROUP the records. Your "Grouping" fields would be the Date, Main ID and Sub ID and then use an aggregator on the other fields (SUM, MAX, etc) to consolidate the nulls and the values into a single line.

1. First append

2. Then Group

Note that you use List.SUM or List.Max in the aggregations so the nulls dont cause errors.

Table.Group(Source, {"Date", "Main ID", "Sub ID"}, {{"sent", each List.Max([sent]), type nullable text}, {"delivered", each List.Max([delivered]), type nullable text}, {"Opens", each List.Max([Opens]), type nullable text}, {"Clicks", each List.Max([Clicks]), type nullable text}})