Distinct count from a semicolon separated string and reversing the data

Hi,

Here is one way to do this:

Data:

PowerQuery:


let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjJU0lFyNrR2NrJ2NlGK1QGKGIFEgFxjCNcYqgAqawLmKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Risk = _t, Control = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Risk", type text}, {"Control", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Control", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Control.1", "Control.2", "Control.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Control.1", type text}, {"Control.2", type text}, {"Control.3", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Risk"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Value"}, {{"New", each _, type table [Risk=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [New][Risk]),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
#"Removed Columns1" = Table.RemoveColumns(#"Extracted Values",{"New"})
in
#"Removed Columns1"




End result:

Now you can simply use distinctcount in a dax measure or aggregation option.

e.g.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/