Freetext data mapping into pre-defined values or "others"

[vpsoini]

Hello.

I'm trying to list models of sold units into a table, but the issue is that model name/number is a freetext field, so sometimes values are not excatly correct.  I have ledger table, where item sales are listed with columns:

Date/Model/Quantity/Price  (below samples with Tesla examples) , where Model column is not a drop-down with pre-defined values, but freetext field

Date	Model	Quantity	Amount
21.02.2023	Model 3	1	50000
19.02.2023	Model X	2	200000
16.02.2023	3	1	50000
02.02.2023	S	1	100000
24.01.2023	X-model	1	100000
15.01.2023	big sedan	1	125000
13.1.2023	Mdel 3	1	55000

And another "helper table" where I have tried to uniform some of the manually entered "wrong" values into right models / model series. This table has e.g. columns

Original model/uniformed model/model series  (once again with Tesla examples)

Original model	Uniformed Model	Series
Model 3	Model 3	3
3-model	Model 3	3
3	Model 3	3
Model X	Model X	X
X-model	Model X	X
X	Model X	X
Model S	Model S	S
S-model	Model S	S
S	Model S	S

These 2 tables are connected with Model - Original Model link and I'm using "uniformed model" as my key in the table visual.

Since the model in the sales ledger table is freetext, users can have whatever kind of typos etc in that field and if I don't have that corresponding typo defined in the helper table, that value will not be calculated within that table. In my example ledger those 2 earliest values have model written incorreclty in a way, which is not defined in the helper table, so they would not be visual in my table

So the question is:  what would be the easiest way to define value "undefined" into the table visual, where all those model "typos" from ledger could be collected in order to get total sales values.  The example table visual could be e.g. total sum:

Uniformed model	Sales
Model 3	100000
Model S	100000
Model X	300000
undefined	180000
Total	680000

So how can I define something, that can be basically anything (but those pre-defined values), into one group named "undefined" ?

Thanks a lot for your solution proposals 😊

[v-yangliu-msft · Accepted solution]

Hi  @vpsoini ,

Here are the steps you can follow：

1. Create calculated column.

Flag =
var _1=
DISTINCT(
SELECTCOLUMNS(
    'Table2',"Uniformed Model","undefined"))
var _2=
DISTINCT('Table2'[Uniformed Model])
return
UNION(
    _2,_1)

2. Create calculated table.

Sales =
var _groupcolumn =
SELECTCOLUMNS(
    FILTER('Table2','Table2'[Uniformed Model]=EARLIER('Flag'[Uniformed Model])),
    "1",
    [Original model])
var _allcolumn=
SELECTCOLUMNS(
   'Table2',
    "1",
    [Original model])
var _sum=
SUMX(
    FILTER(ALL(Table1),
    'Table1'[Model] in _groupcolumn),[Amount])
return
 IF(
     _sum = BLANK(),
    SUMX(
    FILTER(ALL(Table1),
    NOT('Table1'[Model]) in _allcolumn),[Amount]),_sum)

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly