Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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"
Could you copy paste some sample data with expected results?
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"
Just wow! I can't thank you enough! Works as a charm!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |