New to Power BI. Trying to get a count after splitting columns

[wpott]

Hello,

I am trying to get counts for number of categories related to a finding and/or vice versa, number of findings per category. For example the first finding is listed 3 times because it has 3 different recommendations. If I wanted to get the categories for the entire finding there are only 2 different categories for that entire finding.

Do I need to create separate data sources for that? I am stuck because it is counting that finding 3 times or if I split it the other way then it counts the categories as 6 instead of just the 2.

Oversight Body	Date of Report Release	Fiscal Year of Report Release	Audit No.	Audit Name	Finding Description	Recommendation Description	Issue Category
Internal Audit	6/3/2020	2020	20-01-009	Name 1	A comprehensive contract management plan should be developed.	The unit should document current monitoring activities.	Performance and Compliance Monitoring
Internal Audit	6/3/2020	2020	20-01-009	Name 1	A comprehensive contract management plan should be developed.	The unit should ensure processes and procedures for conducting monitoring activities are established.	Performance and Compliance Monitoring; Information and Outreach
Internal Audit	6/3/2020	2020	20-01-009	Name 1	A comprehensive contract management plan should be developed.	The unit should ensure that all personnel are trained.	Performance and Compliance Monitoring; Information and Outreach

[ryan_mayu · Accepted solution]

what visual do you want to use to display?

you can try to right click the column and change it to count(distcint)

[Anonymous · Accepted solution]

Hi,

Thanks for the solution ryan_mayu offered, and i want to offer some more information for user to refet to.

hello @wpott ,based on your description, you need to transform your table to the following format in power query.

In power query, clich the 'Issue Category' column, then select split column by delimiter. and select split them to rows

After spliting, select remove the trim.

And you can refer to the following code in advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZC9TkMxDIVfxbpzf26LhISYKqYOBQa2qoObmCZS4lw5zn1+3CA6MTAwdLId5xz78/E47FlJGBPsmo86LIbH9cN6O25HS29hOW6W4/hk+Stmgo0lO3AlT0KBuMaZrGIVdAoZGS+UiRWmhAw1lJY8nAk8zZTKRH5l+o9A0DjqT98X17rINZFrzMW6RSJfwGzjHDVSXYFJ30k+i9gcR4Ds4cUWSbGXh5toOC3uB85smhBMUhzVSrWv3Stv7xUM5zrDNwM13l/RAc2BquI5xRq+5/zpEs+w5/5LY+H+7a2pELpwjzfSgAqYEkwktTBT6uBmHfmfoE9f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Oversight Body" = _t, #"Date of Report Release" = _t, #"Fiscal Year of Report Release" = _t, #"Audit No." = _t, #"Audit Name" = _t, #"Finding Description" = _t, #"Recommendation Description" = _t, #"Issue Category" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Oversight Body", type text}, {"Date of Report Release", type date}, {"Fiscal Year of Report Release", Int64.Type}, {"Audit No.", type text}, {"Audit Name", type text}, {"Finding Description", type text}, {"Recommendation Description", type text}, {"Issue Category", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Issue Category", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Issue Category"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Issue Category", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Issue Category", Text.Trim, type text}})
in
    #"Trimmed Text"

Then in power bi desktop, create a measure.

Measure = DISTINCTCOUNT('Table'[Issue Category])

Put them to a visual.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu · Accepted solution]

what visual do you want to use to display?

you can try to right click the column and change it to count(distcint)