Forum Discussion
Solved
common character position
Hi! I need help with finding the the common character (or characters) in column (by a category from another colummn).
ex '123G456', '789G321' have common G on third position. So im looking a column to make that would look like that maybe: '...G...'. So the output would look like:
'J....G..'
...KJH...'
etc
DAX or M. Thx for any help!
Try this.
Please see attached file's Query Editor as well for steps
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dcpBCoNADIXhu8xaQhJnxmSttIuK4FLEhbjoBXoAT+PBPEknhYKKs3vv5xtHt8wfcoUL9VPRx8fw+p993QDcVJyJxKYLt4RTRSZEmkXTBrCdyBGo/ABxDoRo8a1yB8oUhepeGLE1YBsA2iNQi8RlFpBFlSoLKosx+AuYvg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"ALL", each _[Column2], type list}}), #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Custom.2", each Text.Combine(Table.AddColumn(Table.FromColumns(List.Transform([ALL],each Text.ToList(_))),"Result",each let mylist=List.Distinct(Record.ToList(_)), mycount=List.Count(mylist) in if mycount=1 then Text.Combine(mylist) else "." )[Result])), #"Expanded ALL" = Table.ExpandListColumn(#"Added Custom2", "ALL") in #"Expanded ALL"
4 Replies
ok
cat1 5CG9046FYK 5CG90….. cat1 5CG9086DN5 5CG90….. cat2 021001a89 ..1001… cat2 981001a12 ..1001… cat2 561001g98 ..1001… cat3 81CQ8200L .1CQ8...L cat3 91CQ8123L .1CQ8...L cat3 11CQ8987L .1CQ8...L cat3 71CQ8654L .1CQ8...L Try this.
Please see attached file's Query Editor as well for steps
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dcpBCoNADIXhu8xaQhJnxmSttIuK4FLEhbjoBXoAT+PBPEknhYKKs3vv5xtHt8wfcoUL9VPRx8fw+p993QDcVJyJxKYLt4RTRSZEmkXTBrCdyBGo/ABxDoRo8a1yB8oUhepeGLE1YBsA2iNQi8RlFpBFlSoLKosx+AuYvg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"ALL", each _[Column2], type list}}), #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Custom.2", each Text.Combine(Table.AddColumn(Table.FromColumns(List.Transform([ALL],each Text.ToList(_))),"Result",each let mylist=List.Distinct(Record.ToList(_)), mycount=List.Count(mylist) in if mycount=1 then Text.Combine(mylist) else "." )[Result])), #"Expanded ALL" = Table.ExpandListColumn(#"Added Custom2", "ALL") in #"Expanded ALL"
