reorganizing column for a table

[syasmin25]

Hello, 

I have the following table.

ID	Subject
101	English
102	English
103	Math
104	Arts
105	Math
101	Arts
101	Science
103	Arts
104	Science
105	English
102	Math

And, I am trying to recreate something like the image below,

Subjects	Number
English	3
Math	3
Arts	3
Science	2
English & Math	5
English, Math, Arts & Science	5

I was thinking of creating conditional columns inside power bi that would seperate the groups at the image above and then do an unpivot. However, my actual dataset is pretty huge and I am trying to avoid the row counts from getting even large. I was wondering if there is any other cleaner way to do this. Please let me know. 

Thank you

[AlB · Accepted solution]

Hi @syasmin25 

Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwVNJRcs1Lz8kszlCK1QGJGGGIGANFfBNLYFwTINexqKQYyjVFlTVElQVxg5MzU/OSU5FMAylQQDIOVYUpVjdB7IgFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Subject = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Subject", type text}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Subject"}, {{"Count", each List.Distinct([ID]), Int64.Type}}),
    EngMath_ = List.Count(List.Distinct(#"Grouped Rows"{[Subject = "English"]}[Count] & #"Grouped Rows"{[Subject = "Math"]}[Count])),
    EngMathArtSci_ = List.Count(List.Distinct(List.Combine(Table.SelectRows(#"Grouped Rows", each List.Contains({"English", "Math", "Arts", "Science"}, [Subject]))[Count]))),
    newRows_ = #table({"Subject","Count"},{{"English & Math"}&{EngMath_}, {"English, Math, Arts & Science"} & {EngMathArtSci_}}),
    res_ = Table.Combine({Table.TransformColumns(#"Grouped Rows", {"Count", each List.Count(_)}), newRows_}),
    #"Changed Type1" = Table.TransformColumnTypes(res_,{{"Subject", type text}, {"Count", Int64.Type}})

in
    #"Changed Type1"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

@syasmin25 

Paste the contents of the first table here, in text-tabular format rather than a screen cap so that it can be copied

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[syasmin25]

However, I was able to add the tables all the way at the top. Thank you

[syasmin25]

It won't allow me to post the table in tabular format.

[syasmin25]

I get the following error everytime, 
Your post has been changed because invalid HTML was found in the message body. The invalid HTML has been removed. Please review the message and submit the message when you are satisfied.

[AlB]

Hi@syasmin25 

You can just use the Group by feature in Power Query with Count Rows as operation

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[syasmin25]

How would I count to see if the ID is 2 or more subjects? I would like to create the table like the second image I posted above.