Combining Columns to create Main Columns

[irfan_abdrhman]

The table below is the data that i have which can't be changed until is transformed into power query

What I want it to become is the columns become Material, Type/Grade, Quantity Delivered and other columns like MRDO NO, MRDO DATE is populated. Inside Material has columns : Material 1, Material 2, Material 3,... Material 10, and same goes to Type/Grade and Quantity Delivered and i want them to match in a row where each row has the correct MRDO NO, MRDO DATE , MATERIAL (n), Type/Grade (n), and Quantity Delivered (n) where (n) is the same number per row. Below is the expected outcome

May I know if this is workable, and the possible workaround(s), Thank you.

[dufoq3]

Hi @irfan_abdrhman,

for future requests provide sample data in usable format (not as a screenshot). If you don't know how to do it - read note below my post.

If you don't know how to use my query, also read Note below my post.

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZDBCsMgDIZfRTy3JUbt2LFQKUL14NpdpO//GovWUgsONIlJ/H5jjFzwjuuBiYEhoKLDOm0m2M2cIVkBQHYJ09esFCA4l5yGVrOm7ay3fmHz/kkZV7wEfnSRYybWgu121Co9be/nMFlP0QinsFbwyAu8CplWVpKSRQr/z4YS69neBSUE3qgHU+Vqzbxfct0eyye0xnqhbJGPHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"MRDO NO" = _t, #"MRDO DATE" = _t, #"Material 1" = _t, #"Type/Grade 1" = _t, #"Quantity Delivered 1" = _t, #"Material 2" = _t, #"Type/Grade 2" = _t, #"Quantity Delivered 2" = _t, #"Material 3" = _t, #"Type/Grade 3" = _t, #"Quantity Delivered 3" = _t, #"Material 4" = _t, #"Type/Grade 4" = _t, #"Quantity Delivered 4" = _t]),
    Transformed = [ a = Table.ColumnNames(Source),
    b = List.Select(a, (x)=> Text.StartsWith(x, "Material")),
    c = List.FirstN(a, 2),
    d = Table.RemoveColumns(Source, c),
    e = List.TransformMany(
            Table.ToRows(Source),
            each List.Split(List.Skip(_, 2), List.Count(List.RemoveMatchingItems(a, c)) / List.Count(b)),
            (x,y)=> List.FirstN(x, 2) & y ),
    f = List.Transform({ 0..List.PositionOf(a, List.Skip(b){0}) -1 }, (x)=> a{x}),
    g = Table.FromRows(e, Value.Type(Table.SelectColumns(Source, f)))
  ][g],
    ChangedType = Table.TransformColumns(Transformed, {
        {"MRDO NO", each Text.PadStart(Text.From(_), 3, "0"), type text},
        {"MRDO DATE", each Date.ToText(Date.From(_), [Format="d-MMM-yy", Culture="en-US"]), type text} })
in
    ChangedType

[irfan_abdrhman]

Hi, sorry for not providing sample data. Click Here for the sample data, please see the other sheets for the expected outcome as well.

[dufoq3 · Accepted solution]

Expected Outcome 1

Result

let
    Source = Excel.Workbook(File.Contents("C:\Downloads\PowerQueryForum\irfan_abdrhman\MRDO.xlsx"), null, true),
    #"Dashboard _Sheet" = Source{[Item="Dashboard ",Kind="Sheet"]}[Data],
    FilteredRows = Table.SelectRows(#"Dashboard _Sheet", each ([Column1] <> null)),
    PromotedHeaders = Table.PromoteHeaders(FilteredRows, [PromoteAllScalars=true]),
    Transformed = [ a = Table.ColumnNames(PromotedHeaders),
    b = List.Select(a, (x)=> Text.StartsWith(x, "Material")),
    c1 = {"MRDO NO", "MRDO DATE"},
    c2 = {"Requestor", "Delivery Location"},
    c3 = List.RemoveMatchingItems(a, c1 & c2),
    d = Table.SelectColumns(PromotedHeaders, c3),
    e = List.TransformMany(
            Table.ToRows(PromotedHeaders),
            each List.Split(List.RemoveLastN(List.Skip(_,2),2), List.Count(c3) / List.Count(b)),
            (x,y)=> List.FirstN(x, 2) & y & List.LastN(x, 2)),
    f = List.Transform({ 0..List.PositionOf(a, List.Skip(b){0}) -1 }, (x)=> a{x}),
    g = Table.FromRows(e, Value.Type(Table.SelectColumns(PromotedHeaders, f & c2))),
    h = Table.TransformColumnNames(g, (x)=> Text.Remove(x, {"0".."9"}))
  ][h],
    FilteredRows2 = Table.SelectRows(Transformed, each ([#"Material "] <> null)),
    ChangedType = Table.TransformColumns(FilteredRows2, {
        //{"MRDO NO", each Text.PadStart(Text.From(_), 3, "0"), type text},
        {"MRDO DATE", each Date.ToText(Date.From(_), [Format="d-MMM-yy", Culture="en-US"]), type text} })
in
    ChangedType

[AlienSx]

i don't see Requestor and Delivery Location columns in original image so we go w/o these columns

    lst = Table.ToList(
        your_table, 
        (w) => List.TransformMany(
            {w},
            (x) => List.Split(List.Skip(List.RemoveNulls(x), 2), 3),
            (x, y) => List.FirstN(x, 2) & y
        )
    ),
    tbl = Table.FromList(
        List.Combine(lst), 
        each _, 
        {"MRDO NO", "MRDO DATE", "Material", "Type/Grade", "Quantity Delivered"}
    )

[irfan_abdrhman]

May I know where do I insert that in?

[AlienSx]

@irfan_abdrhman watch this