power query - data cleaning and analysis

noushad1ali2000 

We may first add Index column, after that custom column value in which is equal to Index if first column starts with "_", otherwise null. Fill that column up to have unique number for each block and group the table but these numbers.

let
    Source = Csv.Document(File.Contents("C:\Test\Source.csv"),[Delimiter=",", Columns=15, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    RemoveDate = Table.Skip(Source,1),
        RenameColumns = Table.RenameColumns(
        RemoveDate,
        {
              {"Column1", "Headers"}
            , {"Column2", "Code"}
            , {"Column3", "Grade"}
            , {"Column5", "ID"}
            , {"Column9", "Title"}
            , {"Column13", "Role"}
        }),
    names = {"Headers", "ID", "Role", "Grade", "Code", "Title"},
    SelectColumns = Table.SelectColumns(RenameColumns,names),

    AddIndex = Table.AddIndexColumn(SelectColumns, "Index", 1, 1, Int64.Type),
    AddGroup = Table.AddColumn(
        AddIndex,
        "Group",
        each if Text.StartsWith([Headers], "_") then [Index] else null),
    FillGroupUp = Table.FillUp(AddGroup,{"Group"}),
    RemoveExtraRows = Table.SelectRows(
        FillGroupUp,
        each ([Group] <> null)),
    RemoveFirstColumn = Table.RemoveColumns(
        RemoveExtraRows,{"Headers"}),
    #"Grouped Rows" = Table.Group(
        RemoveFirstColumn, {"Group"},
        {{"Data", each _}}
    ),
    TransformTables = Table.Combine(
            List.Transform( #"Grouped Rows"[Data], (t) => fnTransformID(t) )
    )
in
    TransformTables

Function is slightly modifies

(tbl as table) =>

let
    RemoveExtra = Table.RemoveColumns(tbl,{"Index", "Group"}),
    names = Table.ColumnNames(RemoveExtra),
    ReplaceSpace = Table.ReplaceValue(RemoveExtra," ",null,Replacer.ReplaceValue,names),
    ReplaceEmpty = Table.ReplaceValue(ReplaceSpace,"",null,Replacer.ReplaceValue,names),
    ReplaceRoleWord = Table.ReplaceValue(ReplaceEmpty,"role",null,Replacer.ReplaceValue,{"Title"}),
    ToColumns = Table.ToColumns( ReplaceRoleWord ),
    RemoveNulls = List.Transform( ToColumns, (q) => List.RemoveNulls(q)),
    GetTable = Table.FromColumns( RemoveNulls, names )
in
    GetTable