Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
However, I was able to add the tables all the way at the top. Thank you
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
52 | |
21 | |
12 | |
11 |