Forum Discussion

appsac1's avatar
appsac1
Icon for Helper I rankHelper I
4 years ago
Solved

having trouble with local model formula

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

ColorValue

Blue

1
Blue2
Red1
Red2
Yellow1
Yellow2

 

Table2

1Light Blue
2Dark 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

ColorNew Color
BlueLight Blue
BlueDark Blue
RedRed
YellowYellow

 

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.

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago
    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.

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.