Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Unpivoting related columns

Thanks for wanting to help! It should be a simple one....

 

I currently have a table that looks like this:

 

 

 

Audio

Video

Audio Poor

Video Poor

May

10

8

4

2

June

12

7

2

1

 

And I'm looking for Power Query to give me this:

 

 

Type

Count

Poor Count

May

Audio

10

4

May

Video

8

2

June

Audio

12

2

June

Video

7

1

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

 

This M code works

 

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Audio", Int64.Type}, {"Video", Int64.Type}, {"Audio Poor", Int64.Type}, {"Video Poor", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Month"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if Text.Contains([Attribute],"Poor") = true then "Poor count" else "Count"),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom"," Poor","",Replacer.ReplaceText,{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Custom]), "Custom", "Value"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Month", Order.Descending}})
in
    #"Sorted Rows"

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

 

This M code works

 

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Audio", Int64.Type}, {"Video", Int64.Type}, {"Audio Poor", Int64.Type}, {"Video Poor", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Month"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if Text.Contains([Attribute],"Poor") = true then "Poor count" else "Count"),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom"," Poor","",Replacer.ReplaceText,{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Custom]), "Custom", "Value"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Month", Order.Descending}})
in
    #"Sorted Rows"

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
parry2k
Super User
Super User

Solution attached.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.