search the value in column

[sahgir123]

Column1
My name is P124-3A
hello P3245-22 P6789-AA
hello how you doing
P2345-34 and P4532-T5
this is for test P2353-4r , and P4567-P1 and P5678-PP
Plant is part of life
i have a above column need to create a new column in power bi : word start with P followed by 4 digit then - then 2 digit write a dax code for me if column found patter more than 1 it should be separated by comma
sample output 
P124-3A
P3245-22, P6789-AA

P2345-34,P4532-T5
P2353-4r, P4567-P1, P5678-PP

[danextian · Accepted solution]

Hi @sahgir123 ,

That would be a very complex thing to do in DAX. I would suggest you used Power Query instead. Here's a sample M script to be based in a blank query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYzNCoMwEIRfZcjZPZgftUcfoLCH3sRDwFiFNCkxpfj2TZVCYQ47M9/OMIjrjmAfDusGrqUm1YuxGsTivI9gJbUhKcFN212o/++W+MYeX5jiGu5HzlIVWmnYMIG1UZJu5mjyUuaL5piQ3ZZRUKNIJ1Q/uGmJ69OUuyPmc9PbkL+vT5sy4gy/zk6M4wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Value", each let 
split = Text.Split ( [Column1], " " ),
select = List.Select ( split, each Text.StartsWith ( _, "P" ) ),
check = List.Select ( select, each let x = try Number.From( Text.End(Text.Start ( _, 4 ), 3 ) ) otherwise null in  x <> null ),
combined = Text.Combine ( check, ", " )
in if combined = "" then null else combined, type text )
in
    #"Added Custom"