none
merge 2 tables based on key and dates - complex RRS feed

  • Question

  • in powerquery - given 2 tables, both with a startdate and a key, how to merge them to get a table that includes a row for each valid key/startdate combination? (Table3 below is my desired result):

    Table1.Key Table1.Durablekey Table1.startdate Table1.color Table1.type Table.location
    1A 1D 1/1/2012 Red T1 Boston
    2A 1D 7/1/2012 Red T1 NY
    3A 1D 1/1/2013 Blue T1 LA
    4A 2D 1/1/2012 Yellow T2 Boston
    Table2.Key Table2.Durablekey Table2.startdate Table2.quality
    1B 1D 2/1/2012 Great
    2B 1D 3/1/2012 Good
    3B 1D 7/1/2012 Poor
    4B 1D 2/1/2013 Excellent
    5B 2D 1/1/2012 Good

    Table 3

    Table1.Key Table2.Key START DATE TAble1.color Table1.type Table.location Table2.quality
    1A NA 1/1/2012 Red T1 Boston NA
    1A 1B 2/1/2012 Red T1 Boston Great
    1A 2B 3/1/2012 Red T1 Boston Good
    2A 3B 7/1/2012 Blue T1 NY Poor
    3A 3B 1/1/2013 Blue T1 LA Poor
    3A 4B 1/1/2013 Blue T1 LA Excellent
    4A 5B 1/1/2012 Yellow T2 Boston Good

    Friday, October 3, 2014 8:10 PM

Answers