Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
71 | |
63 | |
40 | |
28 | |
17 |