cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
syasmin25
Helper V
Helper V

reorganizing column for a table

Hello, 

I have the following table.

syasmin25_0-1608012476898.png

 

IDSubject
101English
102English
103Math
104Arts
105Math
101Arts
101Science
103Arts 
104Science
105English
102Math


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

syasmin25_1-1608012505699.png

 

SubjectsNumber
English3
Math3
Arts3
Science2
English & Math5
English, Math, Arts & Science5


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

 

 

1 ACCEPTED SOLUTION
AlB
Super User III
Super User III

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 

 

SU18_powerbi_badge

View solution in original post

7 REPLIES 7
AlB
Super User III
Super User III

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 

 

SU18_powerbi_badge

View solution in original post

AlB
Super User III
Super User III

@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 

 

SU18_powerbi_badge

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.

AlB
Super User III
Super User III

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 

SU18_powerbi_badge

 

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. 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors