Forum Discussion
Helper IICombining 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 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.
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
6 Replies
- AlienSx
Super User
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
- dufoq3
Community Champion
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
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
