none
Append queries of only unique rows RRS feed

  • Question

  • Hi,

    I have two queries where I am receiving data that is structured the same. I link these tables using an order number.

    The first table has a lot of order numbers. The second only has a few order numbers. Some of the order numbers in the second table may already be in the first table.

    Now I want to append these tables, but only the rows from table 2 that are not already in table 1. How can I do this with Power Query/Power Pivot?

    I cannot simply remove duplicate entries, because there maybe legit duplicate order numbers in the table 1, I only want to make sure I do not add any more from table 2.

    Tuesday, December 30, 2014 12:43 AM

Answers

  • hi,

    I think to do that. You make the different order number list from table 2 and filtering the table 2 with this list. After that, you combine the table 1 with filtered tables. Below is the code :

    let
        Table1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Table2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        Filter_List = List.Difference( Table2[Order No], Table1[Order No] ),
        Table2b = Table.SelectRows(Table2, each List.Contains(Filter_List, [Order No] )),
        Final = Table.Combine( {Table1, Table2b} )
    in
        Final

    I have a small sample to share on my OneDrive for your morde detail

    https://onedrive.live.com/edit.aspx?cid=94B99C319E63236A&resid=94B99C319E63236A%21333&app=Excel

    Regards,


    • Marked as answer by Mustikah Tuesday, December 30, 2014 5:19 PM
    Tuesday, December 30, 2014 1:59 AM

All replies

  • hi,

    I think to do that. You make the different order number list from table 2 and filtering the table 2 with this list. After that, you combine the table 1 with filtered tables. Below is the code :

    let
        Table1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Table2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        Filter_List = List.Difference( Table2[Order No], Table1[Order No] ),
        Table2b = Table.SelectRows(Table2, each List.Contains(Filter_List, [Order No] )),
        Final = Table.Combine( {Table1, Table2b} )
    in
        Final

    I have a small sample to share on my OneDrive for your morde detail

    https://onedrive.live.com/edit.aspx?cid=94B99C319E63236A&resid=94B99C319E63236A%21333&app=Excel

    Regards,


    • Marked as answer by Mustikah Tuesday, December 30, 2014 5:19 PM
    Tuesday, December 30, 2014 1:59 AM
  • Hi CDzung, thanks alot that is exactly what I wanted!

    I am very new with Power BI and took a close look at your example file to try to understand how exactly you built this query. E.g. so far I only knew how to use the standard Power Query Import options from file etc., and did not know that you could import two tables in one query. How did you do that? Did you have to write it into the Advanced Query Editor, or is that option available via the standard GUI? Same for the List difference command etc. Do you have to know and manually enter these or is there a shortcut? Are these commands like List.Difference and Selected.rows these DAX functions, or what are they called? I would like to study them more.

    Thanks a lot for your help!

    Tuesday, December 30, 2014 5:26 PM
  • Hi Mustikah,

    As my opinion, if you just use Power Query via the standard GUI. You just use the one part of "Query" word . The "Power" word is to learn Power Query's formula language. Currently, we don't have official name for this language. But Chris Webb who is the author of "Power Query for Power BI and Excel" book call it "M language". if you want to study M language. You need to read above book to have a basic knowledge  and for the hi-technic you to follow the below blogger :

    1. https://cwebbbi.wordpress.com/category/power-query/

    2. http://www.excelguru.ca/blog/category/power-query/

    3. http://www.mattmasson.com/category/power-query/

    4. http://dbexcelaccounting.blogspot.com/

    and download Microsoft document as below :

    https://support.office.com/en-us/article/Learn-about-Power-Query-formulas-6bc50988-022b-4799-a709-f8aafdee2b2f?ui=en-US&rs=en-US&ad=US&fromAR=1

    That is all what I have when to learn M language.

    Regards,

    Wednesday, December 31, 2014 2:18 AM
  • hello friends I have the same case, someone solved it? in my case it has double table and I combine with table2, in table2 I have data with a massive code (2 rows) but when I combine them I return to the same code information from table2. Thank you
    Tuesday, February 6, 2018 6:15 PM