power query - data cleaning and analysis

samuel_kodjoe 

I did that by couple of iteration and approximately, since we use TRIM() exact position most probably is not required.

However, if you are on Excel with dynamic arrays you add in couple of cells one under another

=SEQUENCE(,LEN(A2),0)
and
=MID(A2,SEQUENCE(,LEN(A2)),1)

It looks like

Positions start from 0 and each next position is first space after the previous field. That's one time job if only your system generates the file always in exactly the same format.

If format is different from time time I guess removing of excess spaces could be done by Power Query but that will require M-script coding.

noushad1ali2000 

With some assumptions regarding the logic in source data, and keeping the source in csv file, main query could be

let
    BlockSize = 15,
    Source = Csv.Document(File.Contents("C:\Test\Source.csv"),[Delimiter=",", Columns=15, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    RemoveDate = Table.Skip(Source,1),
    SplitSource = Table.Split( RemoveDate, BlockSize),
    Custom1 = Table.Combine( List.Transform( SplitSource, (t) => fnTransformID(t) ) )
in
    Custom1

which calls the function

(tbl as table) =>

let
    RenameColumns = Table.RenameColumns(
        tbl,
        {
              {"Column2", "Code"}
            , {"Column3", "Grade"}
            , {"Column5", "ID"}
            , {"Column9", "Title"}
            , {"Column13", "Role"}
        }),
    names = {"ID", "Role", "Grade", "Code", "Title"},
    SelectColumns = Table.SelectColumns(RenameColumns,names),
    ReplaceSpace = Table.ReplaceValue(SelectColumns," ",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

Result is

Please check attached file