Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Change and output string value in a table

Hi all,

 

Is there away to trim or change a string from an array and only output particular terms.

 

For example, from the table below, I only want to output the string values of 'Owners' and 'Members' and remove everything else outside of the membership column. Positional wise they will always be at the end.

Current Table

GroupMembership
RedRed 1 Owners
RedRed 2 Owners
RedRed Members
BlueBlue Members
BlueBlue 1 Owners
BlueBlue 1234567 Owners
BlueAccess Type 12345 Owners

 

Expectation if possible:

GroupMembership
RedOwners
RedOwners
RedMembers
BlueMembers
BlueOwners
BlueOwners
BlueOwners

 

"DAX"
DAX ALL PowerBI Excel

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    The below is one of many solutions to create a new table.

    Please check the below picture and the attached pbix file.

     

     

     

     

    Create New Table =
SELECTCOLUMNS (
    ADDCOLUMNS (
        Data,
        "@MembershipNew",
            SWITCH (
                TRUE (),
                CONTAINSSTRING ( Data[Membership], "Owners" ), "Owners",
                CONTAINSSTRING ( Data[Membership], "Members" ), "Members"
            )
    ),
    "@Group", Data[Group],
    "@Membership", [@MembershipNew]
)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks Ajendra, Struggling abit with the walk through.

       

      So copy and renamed the table, in your example to "Help Column"
      When I add the second line it's not autopopulating?

      Cheers!



      • FreemanZ's avatar
        FreemanZ
        Icon for Super User rankSuper User
        3 years ago

        hi Anonymous 

        the suggested solutions are DAX code and supposed to add calculated columns, instead of custom column in Power Query.