Forum Discussion

irfan_abdrhman's avatar
irfan_abdrhman
Helper II
2 years ago
Solved

Combining Columns to create Main Columns

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 o...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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

     

     

dufoq3's avatar
dufoq3
Community Champion
2 years ago

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's avatar
irfan_abdrhman
Helper II
2 years ago

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