topic Re: Combining Columns to create Main Columns in Power Query

Combining Columns to create Main Columns

irfan_abdrhman — Fri, 26 Apr 2024 03:26:02 GMT

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.

Re: Combining Columns to create Main Columns

AlienSx — Fri, 26 Apr 2024 06:38:16 GMT

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"} )

Re: Combining Columns to create Main Columns

irfan_abdrhman — Fri, 26 Apr 2024 07:02:36 GMT

May I know where do I insert that in?

Re: Combining Columns to create Main Columns

AlienSx — Fri, 26 Apr 2024 07:47:41 GMT

irfan_abdrhman watch this

Re: Combining Columns to create Main Columns

dufoq3 — Fri, 26 Apr 2024 08:00:26 GMT

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

Re: Combining Columns to create Main Columns

irfan_abdrhman — Fri, 26 Apr 2024 08:41:44 GMT

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

Re: Combining Columns to create Main Columns

dufoq3 — Fri, 26 Apr 2024 16:21:28 GMT

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