Forum Discussion
reorganizing column for a table
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
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
7 Replies
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
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.
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
It won't allow me to post the table in tabular format.
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.
However, I was able to add the tables all the way at the top. Thank you
