Forum Discussion
Excel Power query - Unpivot columns
Hello,
I am trying to unpivot from a database some columns from one file. I made up an example, where I need to change the format from the first table into the format of the second table below
The data shows packs made of 2 or 3 components, with reference codes and quantity required to produce each pack
| PDT 1 | CODE | Qty | PDT 2 | CODE | Qty | PDT 3 | CODE | Qty |
PACK 1 | xxx | 123 | 2 | yyy | 236 | 4 |
|
|
|
PACK 2 | ddd | 444 | 1 | cvb | 666 | 1 | ret | 888 | 1 |
I am trying to reformat this database the following way.
| PDT | CODE | Qty |
PACK 1 | xxx | 123 | 2 |
| yyy | 236 | 4 |
PACK 2 | ddd | 444 | 1 |
| cvb | 666 | 1 |
| ret | 888 | 1 |
I tried several options, so far no luck
Any help would be welcome
thank you
Nicolas
consider this table and follow this process
right click on the column 1 and pick unpivot other columns to reach
then use the next fromula to split every three rows of table
= Table.Split(#"Unpivoted Other Columns",3)
in the next step use list.transform to reashape each table
= List.Transform(Custom1,each Table.ToRows(Table.Pivot(_,List.Distinct([Attribute]),"Attribute","Value")){0})resulting a list including sublist which reperesent arows of table, so use the next formula to reach the result
= Table.FromRows(Custom2)
the whole code is
let Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"column1", type text}, {"PDT 1", type text}, {"CODE", Int64.Type}, {"Qty", Int64.Type}, {"PDT 2", type text}, {"CODE2", Int64.Type}, {"Qty3", Int64.Type}, {"PDT 3", type text}, {"CODE4", Int64.Type}, {"Qty5", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"column1"}, "Attribute", "Value"), Custom1 = Table.Split(#"Unpivoted Other Columns",3), Custom2 = List.Transform(Custom1,each Table.ToRows(Table.Pivot(_,List.Distinct([Attribute]),"Attribute","Value")){0}), Custom3 = Table.FromRows(Custom2) in Custom3
6 Replies
consider this table and follow this process
right click on the column 1 and pick unpivot other columns to reach
then use the next fromula to split every three rows of table
= Table.Split(#"Unpivoted Other Columns",3)
in the next step use list.transform to reashape each table
= List.Transform(Custom1,each Table.ToRows(Table.Pivot(_,List.Distinct([Attribute]),"Attribute","Value")){0})resulting a list including sublist which reperesent arows of table, so use the next formula to reach the result
= Table.FromRows(Custom2)
the whole code is
let Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"column1", type text}, {"PDT 1", type text}, {"CODE", Int64.Type}, {"Qty", Int64.Type}, {"PDT 2", type text}, {"CODE2", Int64.Type}, {"Qty3", Int64.Type}, {"PDT 3", type text}, {"CODE4", Int64.Type}, {"Qty5", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"column1"}, "Attribute", "Value"), Custom1 = Table.Split(#"Unpivoted Other Columns",3), Custom2 = List.Transform(Custom1,each Table.ToRows(Table.Pivot(_,List.Distinct([Attribute]),"Attribute","Value")){0}), Custom3 = Table.FromRows(Custom2) in Custom3see this video - case 2
Hello, funny enough, I have been watching a few of your videos yesterday, not this one ! I'll have a look and keep you posted, merci
Hi NPJ ,
Thanks for Omid_Motamedise reply.
Here is another waylet Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnB09lYwVNJRqqioAJKGRsZA0giIKysrQSxjMyBpAsRQFKsD1QNSk5KSApI1AcmDzEguSwKSZmZmUH5RagmQtLCwAPNjYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PACK = _t, #"PDT 1" = _t, CODE = _t, Qty = _t, #"PDT 2" = _t, CODE.1 = _t, Qty.1 = _t, #"PDT 3" = _t, CODE.2 = _t, Qty.2 = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"PACK", type text}, {"PDT 1", type text}, {"CODE", Int64.Type}, {"Qty", Int64.Type}, {"PDT 2", type text}, {"CODE.1", Int64.Type}, {"Qty.1", Int64.Type}, {"PDT 3", type text}, {"CODE.2", Int64.Type}, {"Qty.2", Int64.Type}}), Unpivoted = Table.UnpivotOtherColumns(ChangedType, {"PACK"}, "Attribute", "Value"), ExtractedText = Table.TransformColumns(Unpivoted, {{"Attribute", each Text.BeforeDelimiter(_, "."), type text}}), CleanedAttributes = Table.TransformColumns(ExtractedText, {{"Attribute", each Text.Remove(_, {"0".."9", " "}), type text}}), RemovedBlanks = Table.SelectRows(CleanedAttributes, each ([Value] <> null and [Value] <> "")), AddedIndex = Table.AddIndexColumn(RemovedBlanks, "Index", 1, 1, Int64.Type), Grouped = Table.AddColumn(AddedIndex, "GroupIndex", each Number.IntegerDivide([Index] - 1, 3)), Pivoted = Table.Pivot(Table.RemoveColumns(Grouped, {"Index"}), List.Distinct(Grouped[Attribute]), "Attribute", "Value"), GroupedRows = Table.Group(Pivoted, {"PACK"}, {{"AllRows", each _, type table [PACK=nullable text, PDT=nullable text, CODE=nullable text, Qty=nullable number, Index=Int64.Type]}}), Expanded = Table.ExpandTableColumn(Table.AddColumn(GroupedRows, "Custom", each Table.AddIndexColumn([AllRows],"Index",0,1)), "Custom", {"PDT", "CODE", "Qty", "Index"}), FinalTable = Table.RenameColumns(Table.RemoveColumns(Table.AddColumn(Expanded, "PACK_New", each if [Index] = 0 then [PACK] else null),{"PACK"}),{{"PACK_New", "PACK"}}), #"Removed Columns" = Table.RemoveColumns(FinalTable,{"AllRows", "Index"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"PACK", "PDT", "CODE", "Qty"}) in #"Reordered Columns"Final Output
Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly
let Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content], to_list = Table.ToList( Source, (x) => Table.FromColumns( {{x{0}}} & List.Zip(List.Split(List.RemoveNulls(List.Skip(x)), 3)), {"PACK", "PDT", "CODE", "Qty"} ) ), z = Table.Combine(to_list) in zThank you to all for the time spent in responding and solutions made available, I learned from all of them !
Nicolas
