Turn multiple names in a column into one name if it matches a list of names

[Anonymous]

So I have a table with a column that lists multiple names in each row. Only one name in each row is relevant. I want to get rid of the other names, based on a list of names I have stored elsewhere.

So:

Names	Value
Joe, Clara, Sharon	x
Kelly, Clara, John, Barbara	y
Jacob, Kelly	z

I only care about Joe and Kelly, so what I would want to see is:

Names	Value
Joe	x
Kelly	y
Kelly	z

I don't know if I need to change the query or do a calculated column or something else. I tried splitting the columns and creating a new column but I've got a lot of data and a long list of names I want to pull from that names column. I'd rather not list out the several dozen names that should be checked for in each of the 4 newly created columns. Is there a relatively simple way to tackle this?

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Vera_33]

Hi @Anonymous 

If I understand it correctly, you can keep the data simple and clean, here is one way in M. Not sure if you will have Joe and Kelly in the same list, so use list, you can add more names if you care someone else other than Joe and Kelly

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8spP1VFwzkksStRRCM5ILMrPU9JRqlCK1YlW8k7NyamES3rlZ+TpKDglFiUBuUA1lWA1XonJ+Uk6CmClQMEqpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Names = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Names", type text}, {"Value", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "newName", each List.Select(Text.Split([Names],", "), each List.Contains({"Joe","Kelly"}, _))),
    #"Expanded newName" = Table.ExpandListColumn(#"Added Custom", "newName")
in
    #"Expanded newName"

[Greg_Deckler]

@Anonymous One way:

Column = 
  VAR __NamesICareAbout = SELECTCOLUMNS('PeopeWhoMatter'[Names])
  VAR __Text = SUBSTITUE([Names],", ","|")
  VAR __Count = PATHLENGTH(__Text)
  VAR __Table = 
    FILTER(
      ADDCOLUMNS(
        GENERATESERIES(1,__Count,1),
        "__Name",PATHITEM(__Text,[Value])
      ),
      [__Name] IN __NamesICareAbout
    )
RETURN
  CONCATENATEX(__Table,[__Name],", ")

[Anonymous]

@Greg_Deckler Soooo DAX is all I'm comfortable with, so I tried your solution. This is what I input:

Agent =

VAR __NamesICareAbout = ALL(Agents[Name])

VAR __Text = SUBSTITUTE([Assigned To],",","|")

VAR __Count = PATHLENGTH(__Text)

VAR __Table =

FILTER(

ADDCOLUMNS(

GENERATESERIES(1,__Count,1),

"__Name",

PATHITEM(__Text,[IDNumber])

),

[__Name] IN __NamesICareAbout

)

RETURN

CONCATENATEX(__Table,[__Name],";")

The error I get is that "The result of a conversion or arithmetic operation is either too large or too small."
Not sure what that means.

[Greg_Deckler · Accepted solution]

@Anonymous IDNumber is not correc,t you want [Value]

Agent =
VAR __NamesICareAbout = ALL(Agents[Name])
VAR __Text = SUBSTITUTE([Assigned To],",","|")
VAR __Count = PATHLENGTH(__Text)
VAR __Table =
FILTER(
ADDCOLUMNS(
GENERATESERIES(1,__Count,1),
"__Name",
PATHITEM(__Text,[Value])
),
[__Name] IN __NamesICareAbout
)
 
RETURN
CONCATENATEX(__Table,[__Name],";")

You are using the "index" created by GENERATESERIES to pull the correct path item out of the text path. GENERATESERIES creates a single column table with the column name "Value".