Combine 2 Tables - Child Table as daily updated data and need to Add, Update the Records to master

[Anonymous]

Hi All,

  Was trying to combine 2 Excel Tables 

  Master Table: 

Issue	Name	Created	Updated	priority	status	creator	assignee	projectname
A-23277	Auto 1	2021-09-29T08:26:06.111+1300	2021-09-29T08:26:18.464+1300	1	Done	KV	KV	ASP
A-23278	Auto 2	2021-09-29T08:25:29.477+1300	2021-09-29T08:25:38.682+1300	2	Done	KN	KN	ASP
A-23279	Auto 3	2021-09-29T07:38:21.173+1300	2021-09-29T07:38:30.038+1300	3	To Do	KV	KV	GP
A-23280	Auto 4	2021-09-29T06:56:57.444+1300	2021-09-29T09:15:15.002+1300	4	To Do	KN	KN	GP
A-23281	Auto 5	2021-09-29T06:56:42.389+1300	2021-09-29T06:58:07.022+1300	5	Done	KV	KV	GP

  Child Table : 

Issue	Name	Created	Updated	priority	status	creator	assignee	projectname
A-23279	Auto 3	2021-09-30T07:38:21.173+1300	2021-09-29T07:38:30.038+1300	3	To Do	KV	KV	GP
A-23280	Auto 4	2021-09-29T06:56:57.444+1300	2021-09-30T09:15:15.002+1300	4	To Do	KN	KN	GP
A-23282	Auto 6	2021-09-29T06:56:42.389+1300	2021-09-29T06:58:07.022+1300	6	Done	KV	KV	GP

The data for the Child Table keep on changing with New Records or Updated Records based on Issue .

Have Tried the option of Merge Query , but it's not getting the right data.

The Final Output need to New Tables with Data 

Issue	Name	Created	Updated	priority	status	creator	assignee	projectname
A-23277	Auto 1	2021-09-29T08:26:06.111+1300	2021-09-29T08:26:18.464+1300	1	Done	KV	KV	ASP
A-23278	Auto 2	2021-09-29T08:25:29.477+1300	2021-09-29T08:25:38.682+1300	2	Done	KN	KN	ASP
A-23279	Auto 3	2021-09-30T07:38:21.173+1300	2021-09-29T07:38:30.038+1300	3	To Do	KV	KV	GP
A-23280	Auto 4	2021-09-29T06:56:57.444+1300	2021-09-30T09:15:15.002+1300	4	To Do	KN	KN	GP
A-23281	Auto 5	2021-09-29T06:56:42.389+1300	2021-09-29T06:58:07.022+1300	5	Done	KV	KV	GP
A-23282	Auto 6	2021-09-29T06:56:42.389+1300	2021-09-29T06:58:07.022+1300	6	Done	KV	KV	GP

The Output contains Update of Records and Addition of Records (Highlighted in Bold)

Regards,

Venkat

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

You may use Append not Merge:

Then remove duplicates:

Below is the final output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

This is the common Substitution pattern

let
    #"Final Output" = Child &  Table.SelectRows(Master, each not (List.Contains(Child[Issue],[Issue]))),
    #"Sorted Rows" = Table.Sort(#"Final Output",{{"Issue", Order.Ascending}})
in
    #"Sorted Rows"

see attached.