Mapping inside of Power Query

Hi, it will work but you have to have all columns in source. For instance I had to add ST Location 4 and ST Quantity 4. Second issue is with naming. For some columns you have SB Material but for no 4 you have SB_Item 4(you have to rename SB_Item 4 to SB Matierial 4).

 

Result:

 

If you want to preserve more columns (like Link), you have to define them here:

 

Code for Source EXCEL file:

 

let
    Source = Excel.Workbook(File.Contents("C:\Downloads\PowerQueryForum\irfan_abdrhman\MIR.xlsx"), true, true),
    Source_Sheet = Source{[Item="Source",Kind="Sheet"]}[Data],
    FilteredRows = Table.SelectRows(Source_Sheet, each ([#"Delivery/DO Date"] <> null)),
    ColNames = Table.ColumnNames(FilteredRows),
    __PreserveColumns = {"Delivery/DO Date", "Supplier Name", "Yard Location"},
    Transform =
      List.Transform({"SSI", "ST", "SB"}, (var)=>
        [ a = List.Select(ColNames, (x)=> Text.StartsWith(x, var)), //Select Columns
          b = List.Select(a, (x)=> Text.Contains(x, "Material", Comparer.OrdinalIgnoreCase)), //Select contains "Material"
          pairs = List.Count(a) / List.Count(b),
          c = List.TransformMany(
                  List.Transform(List.Zip({ Table.ToRows(Table.SelectColumns(FilteredRows, __PreserveColumns)), Table.ToRows(Table.SelectColumns(FilteredRows, a)) }), List.Combine),
                  each List.Split(List.Skip(_, List.Count(__PreserveColumns)), pairs),
                  (x,y) => List.FirstN(x, List.Count(__PreserveColumns)) & y ),
          d = Table.FromRows(c, __PreserveColumns & List.FirstN(a, pairs)),
          e = Table.TransformColumnNames(d, each Text.Trim(Text.Remove(_, {"0".."9"})))
        ][e]
      ),
    Transform2 = List.FirstN(Transform, 1) & List.Transform(List.Skip(Transform), (x)=> Table.RemoveColumns(x, __PreserveColumns)),
    Combine = [ a = List.Combine(List.Transform(Transform2, (x)=> Table.ColumnNames(x))), //Column Names
    b = List.Combine(List.Transform(Transform2, Table.ToColumns)),
    c = Table.FromColumns(b, a)
  ][c],
    FilteredRows2 = Table.SelectRows(Combine, each ([SSI Material] <> null)),
    RemovedOtherColumns = Table.SelectColumns(FilteredRows2,{"Supplier Name", "SSI Material", "SSI Type/Grade", "Yard Location", "SSI Quantity", "SSI DO No", "SSI Date", "ST Location", "ST Quantity", "SB Quantity"}),
    RenameColumns1 = Table.TransformColumnNames(RemovedOtherColumns, each Text.Combine(List.RemoveMatchingItems(Text.Split(_, " "), {"SSI", "ST", "SB"}), " ")),
    RenamedColumns2 = Table.RenameColumns(RenameColumns1,{{"Quantity1", "Stock Take"}, {"Quantity2", "Balance Quantity"}}),
    ChangedType = Table.TransformColumnTypes(RenamedColumns2,{{"Supplier Name", type text}, {"Material", type text}, {"Type/Grade", type text}, {"Yard Location", type text}, {"Quantity", type number}, {"DO No", type text}, {"Date", type date}, {"Location", type text}, {"Stock Take", type number}, {"Balance Quantity", type number}})
in
    ChangedType

 

 

Code with sample data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7Zhfb4IwEMC/CsFXF9vSoj7iYI5EnAFdshjf9MHMuCWb338UaG2RUv80yuZePC3H3fV+3NFzPrcB7ADcQQBhu23PHvzYC8fpNwRA+ukCEEXSuqNYh0SxDrIbIDq40kovwVRKAajMU2VElZGgTGqUcVlZtSGq7FJlR1BWR52E08DycpVKe1xB5TBTGGThV17f7jabveAhJLPJZBQGsUVz9ubFfubk5XO1tdZby9t9fyxXX+/WYLfeLO1Fey5vaG8+T1q/sCo7O1kIaUzj7FUDMuSrueIQzUCDBleiKZ46ZRGdnkiZT1/xmFcUBQIQKLR/N07OCjFWUMOK1LEyX0dO0RXJX8v8KXAcBgdp4LjHFJJRONlbyFUXUSMSeR04jgZOrw4Of7EZpUNDkvwa9tZcwfFghudRg6d/3caGi0NZ954bG4fj18OB4LqNDeeNTXJ7d42Nwwk0cMSJZeRNgzg90tvZcRfkK5cHJZilcAjlAUtwjDk7Kyw+6Jjcdj0gwgA9aQChakBGk1YiRGdIye+NCN2selwGZ6iBI84UUTgOx0PLnyVTIWVR8fvC0GTjLZKfrGF5pjHosbmCY+oyTM/NxYT+MfWOxSTOicPYew1GQr74woVhcTstt6BTHk9Nemuu2P/TAxgfXMNn8QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Delivery/DO Date" = _t, #"SB Material 1" = _t, #"SB Quantity 1" = _t, #"SB Type/Grade 1" = _t, #"SB Material 2" = _t, #"SB Quantity 2" = _t, #"SB Type/Grade 2" = _t, #"SB Material 3" = _t, #"SB Quantity 3" = _t, #"SB Type/Grade 3" = _t, #"SB Material 4" = _t, #"SB Quantity 4" = _t, #"SB Type/Grade 4" = _t, #"SSI Material 1" = _t, #"SSI DO No 1" = _t, #"SSI Date 1" = _t, #"SSI Quantity 1" = _t, #"SSI Type/Grade 1" = _t, #"SSI Material 2" = _t, #"SSI DO No 2" = _t, #"SSI Date 2" = _t, #"SSI Quantity 2" = _t, #"SSI Type/Grade 2" = _t, #"SSI Material 3" = _t, #"SSI DO No 3" = _t, #"SSI Date 3" = _t, #"SSI Quantity 3" = _t, #"SSI Type/Grade 3" = _t, #"SSI Material 4" = _t, #"SSI DO No 4" = _t, #"SSI Date 4" = _t, #"SSI Quantity 4" = _t, #"SSI Type/Grade 4" = _t, #"ST Material 1" = _t, #"ST Location 1" = _t, #"ST Quantity 1" = _t, #"ST Type/Grade 1" = _t, #"ST Material 2" = _t, #"ST Location 2" = _t, #"ST Quantity 2" = _t, #"ST Type/Grade 2" = _t, #"ST Material 3" = _t, #"ST Location 3" = _t, #"ST Quantity 3" = _t, #"ST Type/Grade 3" = _t, #"ST Material 4" = _t, #"ST Location 4" = _t, #"ST Quantity 4" = _t, #"ST Type/Grade 4" = _t, #"Supplier Name" = _t, #"Yard Location" = _t, Link = _t]),
    ReplaceNulls = Table.TransformColumns(Source, {}, each if _ = "null" then null else _),
    ColNames = Table.ColumnNames(Source),
    __PreserveColumns = {"Delivery/DO Date", "Supplier Name", "Yard Location"},
    Transform =
      List.Transform({"SSI", "ST", "SB"}, (var)=>
        [ a = List.Select(ColNames, (x)=> Text.StartsWith(x, var)), //Select Columns
          b = List.Select(a, (x)=> Text.Contains(x, "Material", Comparer.OrdinalIgnoreCase)), //Select contains "Material"
          pairs = List.Count(a) / List.Count(b),
          c = List.TransformMany(
                  List.Transform(List.Zip({ Table.ToRows(Table.SelectColumns(ReplaceNulls, __PreserveColumns)), Table.ToRows(Table.SelectColumns(ReplaceNulls, a)) }), List.Combine),
                  each List.Split(List.Skip(_, List.Count(__PreserveColumns)), pairs),
                  (x,y) => List.FirstN(x, List.Count(__PreserveColumns)) & y ),
          d = Table.FromRows(c, __PreserveColumns & List.FirstN(a, pairs)),
          e = Table.TransformColumnNames(d, each Text.Trim(Text.Remove(_, {"0".."9"})))
        ][e]
      ),
    Transform2 = List.FirstN(Transform, 1) & List.Transform(List.Skip(Transform), (x)=> Table.RemoveColumns(x, __PreserveColumns)),
    Combine = [ a = List.Combine(List.Transform(Transform2, (x)=> Table.ColumnNames(x))), //Column Names
    b = List.Combine(List.Transform(Transform2, Table.ToColumns)),
    c = Table.FromColumns(b, a)
  ][c],
    FilteredRows2 = Table.SelectRows(Combine, each ([SSI Material] <> null)),
    RemovedOtherColumns = Table.SelectColumns(FilteredRows2,{"Supplier Name", "SSI Material", "SSI Type/Grade", "Yard Location", "SSI Quantity", "SSI DO No", "SSI Date", "ST Location", "ST Quantity", "SB Quantity"}),
    RenameColumns1 = Table.TransformColumnNames(RemovedOtherColumns, each Text.Combine(List.RemoveMatchingItems(Text.Split(_, " "), {"SSI", "ST", "SB"}), " ")),
    RenamedColumns2 = Table.RenameColumns(RenameColumns1,{{"Quantity1", "Stock Take"}, {"Quantity2", "Balance Quantity"}}),
    ChangedType = Table.TransformColumnTypes(RenamedColumns2,{{"Supplier Name", type text}, {"Material", type text}, {"Type/Grade", type text}, {"Yard Location", type text}, {"Quantity", type number}, {"DO No", type text}, {"Date", type date}, {"Location", type text}, {"Stock Take", type number}, {"Balance Quantity", type number}})
in
    ChangedType