Forum Discussion
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 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.
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.
5 Replies
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
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)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.
