Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ymirza
Helper II
Helper II

Distinct count from a semicolon separated string and reversing the data

Hello PBI Community

 

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

RisksControls
Risk 1Control 1; Control 13; Control 6; Control 8
Risk 2Control 1; Control 2; Control 10
Risk 3Control 1; Control 13; Control 8

ymirza_0-1657276420438.png

 

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. 

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

ymirza_1-1657276436722.png

 

Any help would be highly appreciated 🙂 

1 ACCEPTED SOLUTION
ValtteriN
Super User
Super User

Hi,

Here is one way to do this:

Data:

ValtteriN_0-1657277587004.png


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:

ValtteriN_2-1657278276959.png



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/







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
ValtteriN
Super User
Super User

Hi,

Here is one way to do this:

Data:

ValtteriN_0-1657277587004.png


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:

ValtteriN_2-1657278276959.png



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/







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.