March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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
Solved! Go to 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
Thank you to all for the time spent in responding and solutions made available, I learned from all of them !
Nicolas
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
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
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
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
see this video - case 2
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.