Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
NPJ
New Member

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

 

1 ACCEPTED SOLUTION
Omid_Motamedise
Resident Rockstar
Resident Rockstar

consider this table and follow this process

Omid_Motamedise_0-1727213775126.png

 

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)

 

 

Omid_Motamedise_1-1727213852999.png

 

 

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

Omid_Motamedise_3-1727214455750.png

 

 

resulting a list including sublist which reperesent arows of table, so use the next formula to reach the result

 

= Table.FromRows(Custom2)

 

Omid_Motamedise_4-1727214474426.png

 

 

 









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

View solution in original post

6 REPLIES 6
NPJ
New Member

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

Nicolas

AlienSx
Super User
Super User

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
v-heq-msft
Community Support
Community Support

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

vheqmsft_1-1727242521156.png

 

 

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
Resident Rockstar
Resident Rockstar

consider this table and follow this process

Omid_Motamedise_0-1727213775126.png

 

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)

 

 

Omid_Motamedise_1-1727213852999.png

 

 

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

Omid_Motamedise_3-1727214455750.png

 

 

resulting a list including sublist which reperesent arows of table, so use the next formula to reach the result

 

= Table.FromRows(Custom2)

 

Omid_Motamedise_4-1727214474426.png

 

 

 









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
New Member

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
Impactful Individual
Impactful Individual

see this video - case 2

 

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors