Excel Power query - Unpivot columns

[NPJ]

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

[NPJ]

Thank you to all for the time spent in responding and solutions made available, I learned from all of them !

Nicolas

[AlienSx]

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
    z

[Anonymous]

Hi @NPJ ,
Thanks for Omid_Motamedise reply.
Here is another way

let
    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 He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Omid_Motamedise · Accepted solution]

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

[NPJ]

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

[ChandeepChhabra]

see this video - case 2