CODEWITHSUNDEEP
mergerangepowerquery
Luke_0
Luke_0

Reputation: 855

PowerQuery Join Table by Alphanumeric Min/Max Range

I have a parent table and a child table. The parent table only lists ranges of attributes. I'm looking to merge the two to create a proper hierarchy, but I need a way to filter the child table by the parent range first, I believe.

Here is a sample of the parent table:

parent_item     start_attribute     end_attribute
A               10                  120
B               130                 130
C               140                 200

And the child table:

child_item      child_attribute
U               10
V               50
W               60
X               130
Y               140
Z               150

The output table I'd be looking for is such:

parent_item     child_item
A               U
A               V
A               W
B               X
C               Y
C               Z

To further confuse things, the attributes are alphanumeric, which eliminates uses a List.Generate() function I believe. Here is my pseudo code as I'd see it working:

Table.AddColumn(
    #"parent_table", 
    "child_item",
    each 
        Table.SelectRows(
            child_table, 
            each ([child_attribute] <= EARLIER(end_attribute) and [child_attribute]>= EARLIER(start_attribute) )
            )
    )

This is a simplification as the child table actually contains five attributes and the parent table contains five respective attribute ranges.

Upvotes: 0

Views: 97

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40244

I found this blog post, which held the key to referencing the current row environment. The main takeaway is this:

Each is a keyword to create simple functions. Each is an abbreviation for (_) =>, in which the underscore represents (if you are in a table environment, as we are) the current row.

Using a new function C for child_table, we can write

= Table.AddColumn(#"parent_table", "child_table", each
      Table.SelectRows(Child, (C) =>
          C[child_attribute] >= [start_attribute] and
          C[child_attribute] <= [end_attribute]))

or more explicitly as

= Table.AddColumn(#"parent_table", "child_table", (P) =>
      Table.SelectRows(Child, (C) =>
          C[child_attribute] >= P[start_attribute] and
          C[child_attribute] <= P[end_attribute]))

Once you add this column, just expand the child_item column from your new child_table column.

Upvotes: 1

Alexis Olson
Alexis Olson

Reputation: 40244

One possible approach is to do a full cross join and then filter out the rows you don't want.

  1. Create a custom column on both tables with a constant value of, say, 1.

  2. Merge the Child table into the Parent table matching on the new column.

Cross Join

  1. Expand out the Child table to get a table like this:

Expand Child

  1. Create a custom column with all your desired logic. For example,

    if [child_attribute] >= [start_attribute] and [child_attribute] <= [end_attribute] then 1 else 0

  2. Filter out just the 1 values in this new column.

  3. Remove all other columns except for parent_item and child_item.

Upvotes: 1

Related Questions