Forum Discussion
search the value in column
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
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"
1 Reply
- danextian
Super User
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"