Change and output string value in a table

[Anonymous]

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

Group	Membership
Red	Red 1 Owners
Red	Red 2 Owners
Red	Red Members
Blue	Blue Members
Blue	Blue 1 Owners
Blue	Blue 1234567 Owners
Blue	Access Type 12345 Owners

Expectation if possible:

Group	Membership
Red	Owners
Red	Owners
Red	Members
Blue	Members
Blue	Owners
Blue	Owners
Blue	Owners

[Ajendra]

Hey,

Try this one!

[Anonymous]

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]

hi @Anonymous 

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

[Jihwan_Kim]

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]
)