Question about renaming / creating sub categories

G

gpierson2018

New Member
Joined
Nov 17, 2018
Messages
3
Hi, can anyone help me with this in Power Query? I'm trying to get data like this first table (Table 1) to look like (Table 2).

I understand how to unpivot and pivot in Power Query (and it doesn't work here - I get an error with Sally's hobby)

What I don't understand is how to go through and rename an Attribute that is repeated more than once per unique person/entry, as in my example, "Hobby".

I don't even know what it's called I'm trying to do. This isn't "grouping" I don't believe.
Any help would be appreciated!

..................


[TABLE="width: 384"]
<tbody>[TR]
[TD="width: 64, align: center"]TABLE 1[/TD]
[TD="width: 64, align: center"][/TD]
[TD="width: 64, align: center"][/TD]
[TD="width: 64, align: center"][/TD]
[TD="width: 64, align: center"][/TD]
[TD="width: 64, align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Name[/TD]
[TD="align: center"]Attribute[/TD]
[TD="align: center"]Entry[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Tom[/TD]
[TD="align: center"]Age[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Tom[/TD]
[TD="align: center"]Eyes[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Tom[/TD]
[TD="align: center"]Hobby[/TD]
[TD="align: center"]Bowling[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Bob[/TD]
[TD="align: center"]Age[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Bob[/TD]
[TD="align: center"]Eyes[/TD]
[TD="align: center"]Brown[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Sally[/TD]
[TD="align: center"]Age[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Sally[/TD]
[TD="align: center"]Eyes[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Sally[/TD]
[TD="align: center"]Hobby[/TD]
[TD="align: center"]Fishing[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Sally[/TD]
[TD="align: center"]Hobby[/TD]
[TD="align: center"]Sewing[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Sally[/TD]
[TD="align: center"]Hobby[/TD]
[TD="align: center"]Singing[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65"]TABLE 2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]Name[/TD]
[TD="class: xl66"]Age[/TD]
[TD="class: xl66"]Eyes[/TD]
[TD="class: xl66"]Hobby_1[/TD]
[TD="class: xl66"]Hobby_2[/TD]
[TD="class: xl66"]Hobby_3[/TD]
[/TR]
[TR]
[TD="class: xl65"]Tom[/TD]
[TD="class: xl66"]40[/TD]
[TD="class: xl66"]Blue[/TD]
[TD="class: xl66"]Bowling[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]Bob[/TD]
[TD="class: xl66"]45[/TD]
[TD="class: xl66"]Brown[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]Sally[/TD]
[TD="class: xl66"]35[/TD]
[TD="class: xl66"]Blue[/TD]
[TD="class: xl66"]Fishing[/TD]
[TD="class: xl66"]Sewing[/TD]
[TD="class: xl66"]Singing[/TD]
[/TR]
</tbody>[/TABLE]


 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
you can try

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Name", "Attribute"}, {{"Count", each _, type table}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Attribute]), "Attribute", "Count"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Age.1", each Table.Column([Age],"Entry")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Eyes.1", each Table.Column([Eyes],"Entry")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Hobby.1", each Table.Column([Hobby],"Entry")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom2", {"Age.1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Sorted Rows" = Table.Sort(#"Extracted Values",{{"Age.1", Order.Ascending}}),
    #"Extracted Values1" = Table.TransformColumns(#"Sorted Rows", {"Eyes.1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Extracted Values2" = Table.TransformColumns(#"Extracted Values1", {"Hobby.1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Extracted Values2", {{"Hobby.1", ""}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Errors", "Hobby.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Hobby.1.1", "Hobby.1.2", "Hobby.1.3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Age", "Eyes", "Hobby"})
in
    #"Removed Columns"[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Name[/td][td=bgcolor:#5B9BD5]Attribute[/td][td=bgcolor:#5B9BD5]Entry[/td][td][/td][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]Age.1[/td][td=bgcolor:#70AD47]Eyes.1[/td][td=bgcolor:#70AD47]Hobby.1.1[/td][td=bgcolor:#70AD47]Hobby.1.2[/td][td=bgcolor:#70AD47]Hobby.1.3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Tom[/td][td=bgcolor:#DDEBF7]Age[/td][td=bgcolor:#DDEBF7]
40​
[/td][td][/td][td=bgcolor:#E2EFDA]Sally[/td][td=bgcolor:#E2EFDA]35[/td][td=bgcolor:#E2EFDA]Blue[/td][td=bgcolor:#E2EFDA]Fishing[/td][td=bgcolor:#E2EFDA]Sewing[/td][td=bgcolor:#E2EFDA]Singing[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Tom[/td][td]Eyes[/td][td]Blue[/td][td][/td][td]Tom[/td][td]40[/td][td]Blue[/td][td]Bowling[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Tom[/td][td=bgcolor:#DDEBF7]Hobby[/td][td=bgcolor:#DDEBF7]Bowling[/td][td][/td][td=bgcolor:#E2EFDA]Bob[/td][td=bgcolor:#E2EFDA]45[/td][td=bgcolor:#E2EFDA]Brown[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Bob[/td][td]Age[/td][td]
45​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Bob[/td][td=bgcolor:#DDEBF7]Eyes[/td][td=bgcolor:#DDEBF7]Brown[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Sally[/td][td]Age[/td][td]
35​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Sally[/td][td=bgcolor:#DDEBF7]Eyes[/td][td=bgcolor:#DDEBF7]Blue[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Sally[/td][td]Hobby[/td][td]Fishing[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Sally[/td][td=bgcolor:#DDEBF7]Hobby[/td][td=bgcolor:#DDEBF7]Sewing[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Sally[/td][td]Hobby[/td][td]Singing[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

maybe there is any shorter way so you need to analyse and try your own :)
 
Upvote 0
Probably this is even more complicated.I created two separate queries for personal data and hobbies then merged them.

Query named Hobbies

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each ([Attribute] = "Hobby")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Name"}, {{"Count", each _, type table}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Hobbies", each [Count][Entry]),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Hobbies", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Removed Columns1" = Table.RemoveColumns(#"Extracted Values",{"Count"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Name", type text}})
in
#"Changed Type"


Query named PersonalData

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each ([Attribute] <> "Hobby")),
#"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute]), "Attribute", "Entry"),
#"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Age", Int64.Type}, {"Name", type text}, {"Eyes", type text}})
in
#"Changed Type"

Query named MergeData

let
Source = Table.NestedJoin(PersonalData,{"Name"},Hobbies,{"Name"},"Hobbies",JoinKind.LeftOuter),
#"Expanded Hobbies" = Table.ExpandTableColumn(Source, "Hobbies", {"Hobbies"}, {"Hobbies.1"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Hobbies", "Hobbies.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Hobbies.1.1", "Hobbies.1.2", "Hobbies.1.3"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Hobbies.1.1", type text}, {"Hobbies.1.2", type text}, {"Hobbies.1.3", type text}})
in
#"Changed Type"
 
Upvote 0
Thanks
This is slightly tidier but still not perfect:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Name"}, {{"Count", each _, type table}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Entry]),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"Age", "Eyes"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Eyes", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"Eyes", "Hobby"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Age", Int64.Type}, {"Eyes", type text}, {"Hobby", type text}}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type", "Hobby", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Hobby.1", "Hobby.2", "Hobby.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Hobby.1", type text}, {"Hobby.2", type text}, {"Hobby.3", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Count"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Name", type text}})
in
#"Changed Type2"
 
Upvote 0
IMHO your code is ok :)
I usually use (or not) #"Change Type" with end result but this is not a problem
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

Ironman
  • Question Question
Creating dynamic chart showing last 90 calendar days entries
2 3 4
Replies
38
Views
4K
Ironman
Ironman
Skrej
  • Question Question
Modify table borders generated by HTML macro
Replies
1
Views
584
Skrej
Skrej

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top