Define Headers with dephased rows value

Serricardo 

After removing first 4 rows, you can click on 'Fill Down' option in Power Query so that blank rows in each column under the header will be filled with the same name and then you can remove the unwanted rows from the top. Except 1 row which you want to promote as headers.

Hello Serricardo,

here is the code to move the five columns row by row. In combination with tharunkumarRTK's solution you should get the result. I tried to make my code as "simple" or easily expandable as possible. However, these are work steps that should be better corrected and checked in the source (Excel file)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZHbasMwDIZfpeS6kEMP26476BvsJpSgxEpmiK1MtjPY009OsjXrxu4KRtb3I4tfclkmNpjqHa2CZJso8FCNa0DrGTuoBsZRO3+rC8ZyVMRVQ2pBAUvmCi4MyPKceF2Pb0EPKMrNGZhUaHxy2ZZJ/rh/OBZ7UfMszdMiy59ivlvleZpdoThIPAcxOtLmTAad8LOmjjYvwCP0rzQJzrOugxYbECuaXstAuIzwRTJDtHda0GFnJIkNGq1AfZfPME+8cn24t+uWwcoVei2xDlHaxVWdwNTEOvy0c7y3HeexlctMe4AwLepPP7+/fD4ta/8ROwA32P/X5PIJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t]),
    MoveColumn9 = List.FirstN(List.Skip(Source[Column9], 1), List.Count(Source[Column9]) - 1) & {null},
    MoveColumn10 = List.FirstN(List.Skip(Source[Column10], 1), List.Count(Source[Column10]) - 1) & {null},
    MoveColumn11 = List.FirstN(List.Skip(Source[Column11], 1), List.Count(Source[Column11]) - 1) & {null},
    MoveColumn12 = List.FirstN(List.Skip(Source[Column12], 1), List.Count(Source[Column12]) - 1) & {null},
    MoveColumn13 = List.FirstN(List.Skip(Source[Column13], 1), List.Count(Source[Column13]) - 1) & {null},
    MoveTable = Table.FromColumns(
        {
            Source[Column1],
            Source[Column2],
            Source[Column3],
            Source[Column4],
            Source[Column5],
            Source[Column6],
            Source[Column7],
            Source[Column8],
            MoveColumn9,
            MoveColumn10,
            MoveColumn11,
            MoveColumn12,
            MoveColumn13,
            Source[Column14]
        },
        type table [
            Column1 = Text.Type,
            Column2 = Text.Type,
            Column3 = Text.Type,
            Column4 = Text.Type,
            Column5 = Text.Type,
            Column6 = Text.Type,
            Column7 = Text.Type,
            Column8 = Text.Type,
            Column9 = Text.Type,
            Column10 = Text.Type,
            Column11 = Text.Type,
            Column12 = Text.Type,
            Column13 = Text.Type,
            Column14 = Text.Type
        ]
    ),
    Headers = Table.PromoteHeaders(MoveTable, [PromoteAllScalars=true]),
    Type = Table.TransformColumnTypes(Headers,{{"num_wenda", Int64.Type}, {"data_venda", type date}, {"data_entrega_prevista", type date}, {"data_entrega", type date}, {"vendedor_cod", Int64.Type}, {"vendor_nom", type text}, {"vendor_supervisor", type text}, {"vendedor_equipe", type text}, {"cliente_cod", type text}, {"cliente_nome", type text}, {"Cliente_segmento", type text}, {"cidade_cod", Int64.Type}, {"cidade_nom", type text}, {"product", type text}})
in
    Type

Best regards from Germany
Manuel Bolz

🤝Follow me on LinkedIn