Need help combining two worksheets

[Rnaval]

I have two worksheets. The second worksheet contains data that are not in the first worksheet (IP addresses) which I would like to identify and then append to the first worksheet. In Excel, using the second worksheet, I search for the IP Addresses that are not found in the first worksheet and then copy and append all of those rows to the first worksheet. I would like to do this in Power BI / Power Query so that I can automate / schedule this.

[cengizhanarslan]

1) Load both worksheets into Power Query

2) Make sure the IP Address columns have the same data type in both tables

3) In Worksheet2, do Merge Queries

4) Match Worksheet2[IP Address] to Worksheet1[IP Address]

5) Choose join type: Left Anti

• This returns only rows from Worksheet2 that do not exist in Worksheet1

6) Append that result to Worksheet1

[Rnaval]

Thanks, how do I perform step 6, is there an option to Append or manually copy and paste to Worksheet1?

[flowingbuckle · Accepted solution]

Merge and Append can be done pretty easily through the PQ UI.

For Merge, you just need to make sure your Unique ID's truly match...same type and same string.

For Append, you ideally want both tables to have columns that match. They don't need to match perfectly, but it starts getting a little weird with how columns get ordered if there aren't a perfect match.

You can Merge or Append into an existing query, or create a new one.

I recommend creating a new one, and then changing the queries that are the source of the Merge/Append to a Connection Only that isn't actually loaded.

[Rnaval]

Thank you very much for your help, all is now working well.

[v-sshirivolu]

Hi @Rnaval ,

I would take a moment to thank @flowingbuckle  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

[m_dekorte]

Hi @Rnaval,

You can try a pattern like this.
This assumes you have two tables in 1 excel workbook, the first table is called _t1 and the second (the table to append to the first) is called _t2. Both these tables have at least 1 column called IP address 

let
    t1 = Excel.CurrentWorkbook(){[Name="_t1"]}[Content],
    t2 = Excel.CurrentWorkbook(){[Name="_t2"]}[Content],
    r = Table.Combine(
        {
            t1,
            Table.RemoveMatchingRows(t2, Table.ToRecords(t1), {"IP address"})
        }
    )
in
    r

Or simply append both tables and remove duplicates from the IP address column...
I hope this is helpful