Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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 @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?
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.
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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |