Forum Discussion

sergiod04's avatar
sergiod04
Frequent Visitor
5 years ago
Solved

Table Join - Number to Number Range

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...
General Comment
Need Help
Tutorial Requests
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    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.

     

FrankAT's avatar
FrankAT
Community Champion
5 years ago

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's avatar
sergiod04
Frequent Visitor
5 years ago

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's avatar
    v-yingjl
    Community Support
    5 years ago

    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.