having trouble with local model formula

[appsac1]

i have a main table that is connected as a local model so i can't add any columns to it. for simplicity, lets say its a table of colors. column 1 is the color (blue, red, yellow), column 2 is a random value. i have a second reference table where i have those values and another attribute.

Table1

Color	Value
Blue	1
Blue	2
Red	1
Red	2
Yellow	1
Yellow	2

Table2

1	Light Blue
2	Dark Blue

what i'm trying to do is pass thru that second table attribute if table1 is a certain color. so:

new color = if table1.color <> "blue" then table2.value2 else table1.color

Color	New Color
Blue	Light Blue
Blue	Dark Blue
Red	Red
Yellow	Yellow

normally i would go into power query, and merge table2 (reference table) with table1 (main table) then add my litle formula into a new column. but the main table is connected as a local model so i can't mess with that table. not sure how to get this done.

[v-yanjiang-msft · Accepted solution]

Hi @appsac1 ,

If you can't add columns in the main table, you can select Merge Queries>Merge Queries as New.

Then add a custom column in the new merged table.

New Color = if [Color]="Blue" then [Table2.Sub_color] else[Color]

Then remove other unwanted columns and select remove duplicates of the whole table.

Get the result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[lbendlin]

connected as a local model so i can't add any columns to it

That is not entirely accurate.  You can add calculated columns to the local model as long as they are entirely drived from the current row.

As for your question - not sure if TREATAS or LOOKUPVALUE are allowed in that scenario. Might be worth a try.