Combining sets of attributes

**marian.gulie** · 06-16-2023, 08:12 AM

Hello there,
I have a small issue combining different sets of attributes.
To be more precisely, I have a table with different movie title let's say, and each movie have 2 genre types:

Movie title Genre 1 Genre 2
Movie 1 Action Adventure
Movie 2 Crime Action
Movie 3 Action Adventure
Movie 4 SF Drama
Movie 5 Comedy Romance
Movie 6 Romance Drama
Movie 7 Drama Crime
Movie 8 SF Adventure
Movie 9 SF Action
Movie 10 Comedy SF

What I would like to create is a pivot table in which to sort by genre (column 2 + column 3) and show all movies of that genre, as bellow:

Genre
Action Movie 1
Movie 2
Movie 3
Movie 9
Adventure Movie 1
Movie 3
Movie 8
Crime Movie 2
Movie 7
Comedy Movie 5
Movie 10
Drama Movie 4
Movie 6
Movie 7
Romance Movie 5
Movie 6
SF Movie 4
Movie 8
Movie 9
Movie 10

Version of Excel used:

Microsoft? Excel? for Microsoft 365 MSO (Version 2208 Build 16.0.15601.20660) 32-bit

Thank you in advance

**JeteMc** · 06-17-2023, 10:43 AM

This proposal employs Get & Transform (Power Query) found on the Data tab.
1. Convert the range of raw data into an Excel table (columns A:B)
2. Use the following Power Query code to produce the green/white table (columns D:E):

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_Genre"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Movie title", type text}, {"Genre 1", type text}, {"Genre 2", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Movie title"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Genre"}})
in
    #"Renamed Columns"

3. Produce the pivot table from the green/white table
Note for future reference, please upload an .xlsx file so that contributors do not have to recreate your data.
Let us know if you have any questions.

**marian.gulie** · 06-19-2023, 09:48 AM

I wonder if there's something that can be automated. If For example I add another row, I have to regenerate the green/white table again

**JeteMc** · 06-19-2023, 09:56 AM

Should not need to regenerate the green white table.
When you add a row to the Excel table (blue/white), select a cell in the pivot table and then select Refresh (twice).
Let us know if you have any questions.

**sandy666** · 06-19-2023, 10:03 AM

you can use external data source and then there is no need to load the query table to the sheet, only once you get the pivot table

**marian.gulie** · 06-21-2023, 01:47 AM

Sorry for the late reply. And thank you for the proposal. I didn't just solved an issue, but learned about something that I didn't know existed. So now I have to go deeper into Power Query, and for this I need to thank you

**sandy666** · 06-21-2023, 01:55 AM

who did you write the last post to?

**JeteMc** · 06-21-2023, 08:44 AM

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

Combining sets of attributes

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Combining sets of attributes

Re: Combining sets of attributes

Re: Combining sets of attributes

Re: Combining sets of attributes

Re: Combining sets of attributes

Re: Combining sets of attributes

Re: Combining sets of attributes

Re: Combining sets of attributes

Thread Information

Users Browsing this Thread

Similar Threads

Combining different sets of data

Combining two sets of data

Combining 2 sets of tables

Combining two data sets

Combining 2 sets of code

combining two data sets

Combining two sets of data

Bookmarks

Bookmarks

Posting Permissions