Forum Discussion
Solved
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 ...
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
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"