Select rows based on start and end row

Hi Isma ,

 

In Power Query create a new blank query, go to Advanced Editor andpaste the following over the default code to follow the steps I took:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg5xDArR9XfTdfP0cVWK1UERcfVxCQaLZaQmpqQWGSKxjZDYxmC2q58Lmi4wEeLp6wo20tXFtqKiAtUKF8cQR7BISmJJoqEOiDQCk8ZwURMw3xRMmsFFzcF8CzBpiTDBACxgCDHJ0AjZXXCrQA5y8/TzDPZAchHc8aBAiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    filterUnwanted = Table.SelectRows(chgTypes, each [Column1] <> "" and [Column1] <> "START-OF-FILE" and [Column1] <> "START-OF-FIELDS" and [Column1] <> "END-OF-FIELDS" and [Column1] <> "TIMESTARTED=xxx" and [Column1] <> "START-OF-DATA" and [Column1] <> "END-OF-DATA" and [Column1] <> "TIMEFINISHED=xxx" and [Column1] <> "END-OF-FILE"),
    addValueType = Table.AddColumn(filterUnwanted, "valueType", each if Text.Contains([Column1], ",") then "data" else "header"),
    headerTable = Table.SelectRows(addValueType, each ([valueType] = "header")),
    listHeaders = Table.Group(headerTable, {"valueType"}, {{"Column1", each Text.Combine([Column1], ",")}}),
    dataTable = Table.SelectRows(addValueType, each ([valueType] = "data")),
    appendHeadersAndData = Table.Combine({listHeaders, dataTable}),
    remValueTypeCol = Table.RemoveColumns(appendHeadersAndData,{"valueType"}),
    splitColsByComma = Table.SplitColumn(remValueTypeCol, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    promTableHeads = Table.PromoteHeaders(splitColsByComma, [PromoteAllScalars=true])
in
    promTableHeads

 

Summary:

1) Filter out any row that we know we don't want, e.g. "END-OF-FIELDS" etc.

2) Classify the remaining values - if cell contains "," then assume data, else header.

3) Split this table into separate header and data tables, and combine headers into comma-separated list.

4) Append comma-separated headers back onto data table.

5) Split into columns by comma delimiter.

6) Promote first row to headers.

 

This gives me the following output:

 

Pete