Table Join - Number to Number Range

[sergiod04]

Hello, 

I am hoping I can get some help. I have two tables. One with a list of numeric codes and another one with a numeric code range and it's corresponding category. I need to join those tables or merge them, whichever is easier but don't really know how to perform that join. Here's a example of the data

          TABLE A                                                                                             

Code                 Code Description                          

120                    Snickers                                                    

121                    Kit Kat                                                      

122                    Twix

201                    Sours

210                    Gummies

356                    Candy Corn

TABLE B

Code Range Start          Code Range End            Category 

100                             199                         Chocolate-based

200                             299                         Gummy-based 

300                             399                         Garbage candy

DESIRED TABLE C

Code                 Code Description                Category               

120                    Snickers                            Chocolate-based                        

121                    Kit Kat                               Chocolate-based                       

122                    Twix                                  Chocolate-based

201                    Sours                                Gummy-based

210                    Gummies                          Gummy-based

356                    Candy Corn                       Garbage candy

Any help would be greatly appreciated. 

[FrankAT]

Hi @sergiod04 

with your sample data you can do it like this:

Category = 
CALCULATE(
    MAX('Table B'[Category ]), 
    FILTER(
        'Table B', 
        MAX('Table A'[Code]) >= 'Table B'[Code Range Start] && 
        MAX('Table A'[Code]) <= 'Table B'[Code Range End]
    )
)

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[sergiod04]

Hi @FrankAT , 

I seem to be having an issue. I did not realize but there are a few alpha numeric codes i need to exclude (since a matching code range is not available on Table B). How would i do that? 

[v-yingjl · Accepted solution]

Hi @sergiod04 ,

Based on your description, I have created Table A like this, Table B is the same as yours:

So you want to create Table C which is the same as your initiall expected output witout alpha numeric codes in table A, like this:

In this case, the default code column of tableA is text type, and the code start and code end columns of tableB are numeric type. Create a new calculated tableC like this:

Table C = 
ADDCOLUMNS (
    FILTER (
        ALL ( 'Table A' ),
        'Table A'[Code] >= CONVERT ( MIN ( 'Table B'[Code Range Start] ), STRING )
            && 'Table A'[Code] <= CONVERT ( MAX ( 'Table B'[Code Range End] ), STRING )
    ),
    "Category",
        CALCULATE (
            MAX ( 'Table B'[Category] ),
            FILTER (
                ALL ( 'Table B' ),
                'Table A'[Code] >= CONVERT ( 'Table B'[Code Range Start], STRING )
                    && 'Table A'[Code] <= CONVERT ( 'Table B'[Code Range End], STRING )
            )
        )
)

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sergiod04]

Hi @HotChilli  and thank you for your help. So i am getting an error and i think it may be because i have a few alphanumeric codes in there. How would i exclude those from this calculation? 

[HotChilli]

This is a measure, so if you drag the 2 columns from TableA on to a visual :

MeasureA = VAR _code = MIN(TableA[Code])
RETURN
CALCULATE(MIN(TableB[Category]), FILTER(TableB, TableB[Code Range Start] < _code && _code < TableB[Code Range End]))

 You'll need the Code and range values to be whole number type