none
Join two Power Query queries based on range of numbers RRS feed

  • Question

  • Hi, been using Power Query for a while now without getting into anything too complicated but now need to do something a little more advanced!

    I have 2 queries - 'Users' and 'IP Lookup' - 'Users' has two columns, [user_id] and [ip_number] while 'IP Lookup' has 3, [IPStart], [IPFinish] and [Country].

    What I am looking to do is return the country for the user based on the IP being between [IP Start] and [IP Finish].

    Note - the IP numbers are integers derived from the usual xxx.xx.xx.xx type configuration.

    Any ideas?

    Jacob


    Jacob | Please mark helpful posts and answers

    Thursday, June 12, 2014 3:27 PM

Answers

  • You could try removing the try/otherwise in the function definition. This may give you a clear error message.
    • Marked as answer by Jacob Barnett Monday, June 16, 2014 9:20 PM
    Thursday, June 12, 2014 5:47 PM
  • I think the best way to do this is to define a function that does the lookup against the IP table and then apply it to each row of the users table. Here's some sample code which does that:

    let
        Users = Table.FromRows({{1, 100}, {2, 1000}, {3, 10000}}, {"user_id", "ip_number"}),
        #"IP Lookup" = Table.FromRows({{1, 250, "Gondor"}, {251, 500, "Rohan"}, {501, 750, "Arnor"}, {751, 1000, "Mordor"}}, {"IPStart", "IPFinish", "Country"}),
        LookupIP = (ip) => try Table.First(Table.SelectRows(#"IP Lookup", each ip >= [IPStart] and ip <= [IPFinish]))[Country] otherwise null,
        AugmentedUsers = Table.AddColumn(Users, "Country", each LookupIP([ip_number]))
    in
        AugmentedUsers

    Thursday, June 12, 2014 4:50 PM

All replies

  • I think the best way to do this is to define a function that does the lookup against the IP table and then apply it to each row of the users table. Here's some sample code which does that:

    let
        Users = Table.FromRows({{1, 100}, {2, 1000}, {3, 10000}}, {"user_id", "ip_number"}),
        #"IP Lookup" = Table.FromRows({{1, 250, "Gondor"}, {251, 500, "Rohan"}, {501, 750, "Arnor"}, {751, 1000, "Mordor"}}, {"IPStart", "IPFinish", "Country"}),
        LookupIP = (ip) => try Table.First(Table.SelectRows(#"IP Lookup", each ip >= [IPStart] and ip <= [IPFinish]))[Country] otherwise null,
        AugmentedUsers = Table.AddColumn(Users, "Country", each LookupIP([ip_number]))
    in
        AugmentedUsers

    Thursday, June 12, 2014 4:50 PM
  • Curt, thanks for this, it's certainly an eye opener for what can be done!

    I adapted your code to match the actual names in my model and removed the original lines that create your table to give me this:

    let
      
    LookupIP = (ip) => try Table.First(Table.SelectRows(#"IP Lookup", each ip >= [Start] and ip <= [Finish]))[Country Code] otherwise null,
        AugmentedUsers = Table.AddColumn(Users, "Country Code", each LookupIP([IP Number]))
    in
        AugmentedUsers

    Presently I get null for every value! Any ideas?

    Thanks again

    Jacob


    Jacob | Please mark helpful posts and answers

    Thursday, June 12, 2014 5:43 PM
  • You could try removing the try/otherwise in the function definition. This may give you a clear error message.
    • Marked as answer by Jacob Barnett Monday, June 16, 2014 9:20 PM
    Thursday, June 12, 2014 5:47 PM
  • Thanks Curt, that did the trick!

    Jacob


    Jacob | Please mark helpful posts and answers

    Monday, June 16, 2014 9:20 PM
  • Curt -- the inline function above works great - thank you.  As a followup -  trying to understand why the approach of inserting code from the function beginning with 'Table.SelectRows ..." fails.  Can you suggest an approach that would not require the inline function.

    Thanks - Drewbbc


    aabc

    Monday, May 15, 2017 2:17 PM
  • Curt - a follow up question re Multi-criteria joins in "M" 

    Objective - assign the color 'blue' from the simple lookup table - indeally using the inline function concept develped for above

    Transaction Table Elements
    Animal Age Year Color
    cat average 36 blue

    Simple Lookup Table
    id# Animal Age Yr Lwr Uppr Color
    a cat average Y1 47 95 red
    b cat average Y2 31 47 blue
    c cat average Y3 18 30 green
    d dog average Y2 32 48 red
    e dog old Y1 77 116 blue
    f dog young Y2 56 84 blue
    g goats average Y1 53 80 red
    h goats young Y2 44 66 yellow

    In excel, the solution is an multi criteria sumproduct inside of an index match - see below

    =INDEX(LookUp[Color],                    SUMPRODUCT(  (J51 = LookUp[Animal])*                                 (K51 = LookUp[Age])*                                 (L51 >LookUp[Lwr]) *                                 (L51 < LookUp[Uppr]),              MATCH(LookUp[id'#],LookUp[id'#],0)  ) )

    Any help would be appreciated - DrewBbc


    aabc

    Tuesday, June 27, 2017 4:51 PM
  • Curt - finally stumbled into a solution (ie. amended the function statement to include two additional variables) - let me know if you want to see the exact code. 

    Thanks - Drewbbc. 


    aabc

    Thursday, June 29, 2017 12:13 PM