Question about renaming / creating sub categories

[gpierson2018]

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 1
Name	Attribute	Entry
Tom	Age	40
Tom	Eyes	Blue
Tom	Hobby	Bowling
Bob	Age	45
Bob	Eyes	Brown
Sally	Age	35
Sally	Eyes	Blue
Sally	Hobby	Fishing
Sally	Hobby	Sewing
Sally	Hobby	Singing
TABLE 2
Name	Age	Eyes	Hobby_1	Hobby_2	Hobby_3
Tom	40	Blue	Bowling
Bob	45	Brown
Sally	35	Blue	Fishing	Sewing	Singing

<tbody>
</tbody>

 

[sandy666]

you can try

[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]

Name	Attribute	Entry		Name	Age.1	Eyes.1	Hobby.1.1	Hobby.1.2	Hobby.1.3
Tom	Age	40​		Sally	35	Blue	Fishing	Sewing	Singing
Tom	Eyes	Blue		Tom	40	Blue	Bowling
Tom	Hobby	Bowling		Bob	45	Brown
Bob	Age	45​
Bob	Eyes	Brown
Sally	Age	35​
Sally	Eyes	Blue
Sally	Hobby	Fishing
Sally	Hobby	Sewing
Sally	Hobby	Singing

maybe there is any shorter way so you need to analyse and try your own

 

[peter789]

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"

 

[gpierson2018]

Thanks Sandy, this looks hopeful. I will work with this and see what I can do!

 

[gpierson2018]

Thanks, Peter, I will try yours too. I'm working with Sandy's now!

 

[sandy666]

I will work with this and see what I can do!

You are welcome
(this is not optimized code )

 

[peter789]

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"

 

[sandy666]

IMHO your code is ok
I usually use (or not) #"Change Type" with end result but this is not a problem

 

[sandy666]

But what will you do if Sally has one eye Blue and second Green ?