Forum Discussion
Convert table shape - remove columns and add as rows
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
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"Hi,
Look at "Advanced options" and choose Rows, not Columns
https://community.fabric.microsoft.com/t5/Power-Query/Reverse-pivots-with-multiple-columns-Transform-efficiently/m-p/3568934#M116984Stéphane
4 Replies
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"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"
Hi,
Look at "Advanced options" and choose Rows, not Columns
https://community.fabric.microsoft.com/t5/Power-Query/Reverse-pivots-with-multiple-columns-Transform-efficiently/m-p/3568934#M116984Stéphane
Thanks very much, that was a lot easier to do.