Headers split from notepad++ to Excel

On reflection the following changes make more sense re. Step TransformedList

    repeatDelim2 = Text.Repeat( "|", 2 ),
    repeatDelim3 = repeatDelim2 & "|",
    repeatDelim5 = repeatDelim2 & repeatDelim3,
    TransformedList = Table.AddColumn( AddedRowNum, "CustomList", each
        if [Row] = 1
            then  List.Transform( List.FirstN( [Splitted], 2 ),
                    (text) => Text.Combine( {repeatDelim5, Text.Trim(text), repeatDelim5} )
                  )
        else if [Row] = 2
            then List.Transform( List.Repeat( List.FirstN( List.Skip( [Splitted], 2 ), 2 ), 2 ),
                    (text) =>   if Text.StartsWith(text, "Type 1 PCell")
                                then Text.Combine( {repeatDelim3, Text.Trim(text), repeatDelim2} )
                                else Text.Combine( {repeatDelim2, Text.Trim(text) & repeatDelim2} )
                )
        else if [Row] = 3
            then List.FirstN( List.Skip( List.Transform( [Splitted],
                    (text) => if Text.StartsWith(text, "Pcmax") then text else "" ), 3 ), 22
                )
        else List.FirstN( List.Skip( [Splitted], 3 ), 22 ),
        type list
    ),

Hi anupambit1797​ 

Not an easy one 🤔 I had to hard-code some text values like "Pcmax" & "Type 1 PCell", This means the query will only work with other text files formatted the same & those values

// CsvSource
let
    Source = Csv.Document(
        // Don't forget to update the below file path with yours
        File.Contents("C:\Lz\Downloads\headers.txt"),
        [Delimiter=",", Columns=1, Encoding=1252]
    )
in
    Source

// Headers
let
    Source = CsvSource,
    RemovedTopRows = Table.Skip( Source, 2 ),
    TextSplitToList = Table.AddColumn( RemovedTopRows, "Splitted", each
        List.Skip( Text.Split( [Column1], "|" ), 22 ), type list
    ),
    RemovedColumn = Table.RemoveColumns( TextSplitToList, {"Column1"} ),
    AddedRowNum = Table.AddIndexColumn( RemovedColumn, "Row", 1, 1 ),
    TransformedList = Table.AddColumn( AddedRowNum, "CustomList", each
        if [Row] = 1
            then  List.Transform( List.FirstN( [Splitted], 2 ),
                    (text) =>
                        let
                            repeatDelim5 = Text.Repeat( "|", 5 )
                        in
                            Text.Combine( {repeatDelim5, Text.Trim(text), repeatDelim5} )
                )
        else if [Row] = 2
            then List.Transform( List.Repeat( List.FirstN( List.Skip( [Splitted], 2 ), 2 ), 2 ),
                    (text) =>
                        let
                            repeatDelim2 = Text.Repeat( "|", 2 ),
                            repeatDelim3 = repeatDelim2 & "|"
                        in
                            if Text.StartsWith(text, "Type 1 PCell")
                            then Text.Combine( {repeatDelim3, Text.Trim(text), repeatDelim2} )
                            else Text.Combine( {repeatDelim2, Text.Trim(text) & repeatDelim2} )
                )
        else if [Row] = 3
            then List.FirstN( List.Skip( List.Transform( [Splitted],
                    (text) => if Text.StartsWith(text, "Pcmax") then text else "" ), 3 ), 22
                )
        else List.FirstN( List.Skip( [Splitted], 3 ), 22 ),
        type list
    ),
    RemovedOtherColumns = Table.SelectColumns( TransformedList, {"CustomList"} ),
    ListToText = Table.AddColumn( RemovedOtherColumns, "TextToSplit", each
        Text.Combine( [CustomList], "|" ), type text
    ),
    RemovedList = Table.SelectColumns(ListToText, {"TextToSplit"} ),
    SplitByDelimiter = Table.SplitColumn( RemovedList, "TextToSplit",
        Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),
        List.Transform( {1..22}, (num) => "Col" & Text.From(num) )
    )
in
    SplitByDelimiter