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 ...

Need Help

Omid_Motamedise

1 year ago

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

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"

Forum Discussion

Excel Power query - Unpivot columns

Related content

Unpivot Column

Unpivot columns

Pivot / Unpivot?

Unpivot Question

Unpivot