Convert table shape - remove columns and add as rows

[SteveW_Stats]

Hi all

I'm new to PowerBI/Power Query and hope someone can help. I'm not sure what the technical term is for this process that I'm trying to achieve. I'll describe the start & end points as best I can:

My dataset looks like this:

Name	Pets
Jim	Cat
Bob	Dog,Cat,Hamster
Pete	Cat,Dog
Tony	Dog,Bird

I want a row for each person/animal pair. So far I've been able to split the animal column, by delimiter, in Power Query - which gives me this:

Name	Pets1	Pets2	Pets3
Jim	Cat
Bob	Dog	Cat	Hamster
Pete	Cat	Dog
Tony	Dog	Bird

My end goal is for it to look like this:

Name	Pet
Jim	Cat
Bob	Dog
Pete	Cat
Tony	Dog
Bob	Cat
Pete	Dog
Tony	Bird
Bob	Hamster

I'd be most grateful for any guidance.

Thanks

[smpa01 · Accepted solution]

@SteveW_Stats  you can do this

let
    Source = Web.BrowserContents("https://community.fabric.microsoft.com/t5/Power-Query/Convert-table-shape-remove-columns-and-add-as-rows/m-p/3580119#M117343"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE:nth-child(6) > * > TR > :nth-child(1)"}, {"Column2", "TABLE:nth-child(6) > * > TR > :nth-child(2)"}}, [RowSelector="TABLE:nth-child(6) > * > TR"]),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Column2],",")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column2"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom")
in
    #"Expanded Custom"

[slorin · Accepted solution]

Hi,

Look at "Advanced options" and choose Rows, not Columns
https://community.fabric.microsoft.com/t5/Power-Query/Reverse-pivots-with-multiple-columns-Transform...

Stéphane

[SteveW_Stats]

Thanks very much, that was a lot easier to do.

[smpa01 · Accepted solution]

@SteveW_Stats  you can do this

let
    Source = Web.BrowserContents("https://community.fabric.microsoft.com/t5/Power-Query/Convert-table-shape-remove-columns-and-add-as-rows/m-p/3580119#M117343"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE:nth-child(6) > * > TR > :nth-child(1)"}, {"Column2", "TABLE:nth-child(6) > * > TR > :nth-child(2)"}}, [RowSelector="TABLE:nth-child(6) > * > TR"]),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Column2],",")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column2"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom")
in
    #"Expanded Custom"

[SteveW_Stats]

Thanks very much, that was incredibly fast.

I've modified your solution to suit as follows. It worked perfectly.

let
Source = PetData,
#"Changed Type" = Source,
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Pets],",")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Pets"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom")
in
#"Expanded Custom"