Group by columns in DAX

Alright interesting case! I came up with the following solution.

Step 1: Dubplicate the anchor column and split the duplicate column by delimiter, in this case by semi-column. I created a practice table with more than 2 anchors per row, so I got more columns than 2. I don't know how many anchors you can have in that column per row, but I didn't think it would be a maximum of 2.

My original test tableDuplicated the Anchor columnSplitting by delimiter created 4 additional columns

Step two is to create a custom column that checks if the Target column contains any of the values. THis is the formula I used:

if Text.Contains([Targets], Text.From([#"Anchors - Copy.1"])) then "yes" else if Text.Contains([Targets], Text.From([#"Anchors - Copy.2"])) then "yes" else if Text.Contains([Targets], Text.From([#"Anchors - Copy.3"])) then "yes" else if Text.Contains([Targets], Text.From([#"Anchors - Copy.4"])) then "yes" else "no"

This results in another column with "yes" or "error". The errors are because for some reason I couldn't compare a text value (Targets) with null values, see my third screenshot. You can replace errors by 'no' by right clicking on the new column and click 'Replace errors' and replace it with 'no'./ Then remove all columns you don't need anymore (the duplicate, all delimited columns) and you are left with this:

Here my total M:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSrROsk5W0lFKsk5RitUB8cuAvFTrNCgvyTrNOh0okmidbp0KEwOrT0bipYDkYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Anchors"}, {"Column2", "Targets"}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Anchors", "Anchors - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Anchors - Copy", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Anchors - Copy.1", "Anchors - Copy.2", "Anchors - Copy.3", "Anchors - Copy.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Targets", type text}, {"Anchors - Copy.1", type text}, {"Anchors - Copy.2", type text}, {"Anchors - Copy.3", type text}, {"Anchors - Copy.4", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if Text.Contains([Targets], Text.From([#"Anchors - Copy.1"])) then "yes" else if Text.Contains([Targets], Text.From([#"Anchors - Copy.2"])) then "yes" else if Text.Contains([Targets], Text.From([#"Anchors - Copy.3"])) then "yes" else if Text.Contains([Targets], Text.From([#"Anchors - Copy.4"])) then "yes" else "no"),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Custom", "no"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Errors",{"Anchors - Copy.1", "Anchors - Copy.2", "Anchors - Copy.3", "Anchors - Copy.4"})
in
    #"Removed Columns"

Hope this helps! That was fun to do 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Kudo's are welcome 🙂