Distinct count from a semicolon separated string and reversing the data

[ymirza]

Hello PBI Community

I have this dataset with multiple strings separated by semi-colons.

Risks	Controls
Risk 1	Control 1; Control 13; Control 6; Control 8
Risk 2	Control 1; Control 2; Control 10
Risk 3	Control 1; Control 13; Control 8

I want to achieve a Distinct count of these controls; for example, the above should show 6 unique controls.

Distinct Count of Controls

6

Secondly, I want to reverse engineer the same dataset into something like this. 

Control	Risks
Control 1	Risk 1; Risk 2; Risk 3
Control 2	Risk 2
Control 6	Risk 1
Control 8	Risk 1; Risk 3
Control 10	Risk 2
Control 13	Risk 1: Risk 3

Any help would be highly appreciated 🙂 

[ValtteriN · Accepted solution]

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.

Measure = DISTINCTCOUNT(RiskControl[Value])

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/

[ymirza]

Excellent steps. 

My job was done till Removed Columns. Many thanks 🙂

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"}),