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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors