Pivot columns with random multiple rows

[masplin]

ve written a web query to pull out data form a web page like thishttps://www.swissbiotech.org/member/ibr-inc-institute-biopharmaceutical-research.  The problem is that for some field names there is one entry and for others 2 or 3.  I can't think of anyway to unpivot this table.  I've created a column that identifies which rows are headings and and which are data, but can't get any further as there i no key to tell which nes are grouped together. 

so far my code is

let
    Source = Web.Page(Web.Contents("https://www.swissbiotech.org/member/ibr-inc-institute-biopharmaceutical-research")),
    Data0 = Source{0}[Data],
    Children0 = Data0{0}[Children],
    Children2 = Children0{2}[Children],
    Children1 = Children2{1}[Children],
    Children3 = Children1{3}[Children],
    Children4 = Children3{3}[Children],
    Children5 = Children4{1}[Children],
    Children = Children5{5}[Children],
    Children6 = Children{1}[Children],
    Children7 = Children6{1}[Children],
    Children8 = Children7{1}[Children],
    Children9 = Children8{1}[Children],
    #"Filtered Rows" = Table.SelectRows(Children9, each ([Kind] = "Element")),
    #"Expanded Children" = Table.ExpandTableColumn(#"Filtered Rows", "Children", {"Kind", "Name", "Children", "Text"}, {"Kind.1", "Name.1", "Children.1", "Text.1"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Children", each ([Kind.1] = "Element")),
    #"Expanded Children.1" = Table.ExpandTableColumn(#"Filtered Rows1", "Children.1", {"Kind", "Name", "Children", "Text"}, {"Kind.2", "Name.2", "Children", "Text.2"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Children.1",{"Text.2"}),
    #"Filtered Rows3" = Table.SelectRows(#"Removed Other Columns", each ([Text.2] <> null)),
    #"Trimmed Text" = Table.TransformColumns(#"Filtered Rows3",{{"Text.2", Text.Trim, type text}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "Custom", each if Text.EndsWith([Text.2],":") then 0 else 1),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Column"}})
in
    #"Renamed Columns"

Which looks like this with Key Businesses having 2 rows below and Biotech Activities having 3.  Is there any solution for data structured like this as going to be quite random how many rows below with each header. Thew only identifier is headers end in an ":"

Thnaks

Mike

[v-yulgu-msft · Accepted solution]

Hi @masplin,

Please try this:   

let
    Source = Web.Page(Web.Contents("https://www.swissbiotech.org/member/ibr-inc-institute-biopharmaceutical-research")),
    Data0 = Source{0}[Data],
    Children0 = Data0{0}[Children],
    Children2 = Children0{2}[Children],
    Children1 = Children2{1}[Children],
    Children3 = Children1{3}[Children],
    Children4 = Children3{3}[Children],
    Children5 = Children4{1}[Children],
    Children = Children5{5}[Children],
    Children6 = Children{1}[Children],
    Children7 = Children6{1}[Children],
    Children8 = Children7{1}[Children],
    Children9 = Children8{1}[Children],
    #"Filtered Rows" = Table.SelectRows(Children9, each ([Kind] = "Element")),
    #"Expanded Children" = Table.ExpandTableColumn(#"Filtered Rows", "Children", {"Kind", "Name", "Children", "Text"}, {"Kind.1", "Name.1", "Children.1", "Text.1"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Children", each ([Kind.1] = "Element")),
    #"Expanded Children.1" = Table.ExpandTableColumn(#"Filtered Rows1", "Children.1", {"Kind", "Name", "Children", "Text"}, {"Kind.2", "Name.2", "Children", "Text.2"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Children.1",{"Text.2"}),
    #"Filtered Rows3" = Table.SelectRows(#"Removed Other Columns", each ([Text.2] <> null)),
    #"Trimmed Text" = Table.TransformColumns(#"Filtered Rows3",{{"Text.2", Text.Trim, type text}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "Custom Col1", each if Text.EndsWith([Text.2], ":") then 1 else 0),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom Col2", each if Text.EndsWith([Text.2], ":") then [Text.2]&"-" else [Text.2]),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom1", "Index", 1, 1),
    #"Running Total" =Table.AddColumn(#"Added Index","Running Total", each List.Sum(List.Range(#"Added Index"[Custom Col1],0,[Index]))),
    #"Group" = Table.Group(#"Running Total", {"Running Total"}, {{"Custom Col3", each Text.Combine(_[Custom Col2], "   "), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(Group, "Custom Col3", Splitter.SplitTextByDelimiter(":-", QuoteStyle.Csv), {"Custom Col3.1", "Custom Col3.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom Col3.1", type text}, {"Custom Col3.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Running Total"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Custom Col3.1"]), "Custom Col3.1", "Custom Col3.2")
in
    #"Pivoted Column"

I have also uploaded the .pbix file for your reference.   

Best regards,

Yuliana Gu

[masplin]

Ah genius.  I got asfar as the running totla part, but wasn't aware of the Group function to then get all those randow row cells into one cell.

Thanks very much as 10 lines more elegant than what I hacked up

Mike