Forum Discussion

ConnieMaldonado's avatar
ConnieMaldonado
Icon for Responsive Resident rankResponsive Resident
3 years ago
Solved

Change a Field with CALCULATETABLE

I am using CALCULATETABLE to create a table from another table where category = "Split".

Once I have this subset, I want to replace category with "Materials".

Can I do this all in the same statement or what's the best way to do it?

 

Here's the code to create the table:

 

MATERIALSTable =
CALCULATETABLE ( 'MergedTable', 'MergedTable'[Mapping.Category] = "Split" )

 

Thanks!

Need Help
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    Hi ConnieMaldonado ,

     

    If you want to get a modified table directly, I recommend creating a new column in the original table first.

     

    This is my test table:

     

    Create a new column:

    NewCategory = SUBSTITUTE('Table'[Category],"Split","Materials")

     

    Create a new table:

    NewTable = FILTER(SELECTCOLUMNS('Table',"ID",'Table'[ID],"Category",'Table'[NewCategory]),[Category] = "Materials")

     

    Best regards,

    Yadong Fang

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

1 Reply

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi ConnieMaldonado ,

     

    If you want to get a modified table directly, I recommend creating a new column in the original table first.

     

    This is my test table:

     

    Create a new column:

    NewCategory = SUBSTITUTE('Table'[Category],"Split","Materials")

     

    Create a new table:

    NewTable = FILTER(SELECTCOLUMNS('Table',"ID",'Table'[ID],"Category",'Table'[NewCategory]),[Category] = "Materials")

     

    Best regards,

    Yadong Fang

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