Forum Discussion
Unpivot several columns with attribute and value
i take these example
ok problem solve follow below step
1. select ID Articolo and unpivote other column
2. replace value code mat1, code mat2, code mat3, code amt4 to code
and qt mat1, qt amt2, qt amt3, qt mat4 to qt
3. add conditional column like these
which look like these
4. add another conditional column like these
5. copy qt column and paste to new table(qt table) and add index column which look like below
6. now return to original table and add index to original table
7. now merge original table and qt tqble
8. now expand table click on rounded(red) area
by clicking red area open interface and from that unselect index column
press ok which look like these
9. change qt table name to qt and select Id Articolo, code and qt and remove other column and it look like these
10. now filter table and which show those value non-null value
and it look like these
here solution done
don't get confuse with values because i take some different value from you but following my step you got your answer here t attach step screenshort for comfortness
close and apply from home tab and paly with it
Did i answer your question? Mark my post as a solution which help other people to find fast and easily.
Hi ancorte
You can put the following code to advanced editor in power query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQxtVDSUTK3NDAHUgZ6lmZAyszEyBJIGeoZgUhTIwMgBWKamxqAhU2VYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Code 1" = _t, #"Qt 1" = _t, #"Code 2" = _t, #"Qt 2" = _t, #"Code 3" = _t, #"Qt 3" = _t, #"Code 4" = _t, #"Qt 4" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Code 1", Int64.Type}, {"Qt 1", type number}, {"Code 2", Int64.Type}, {"Qt 2", type number}, {"Code 3", Int64.Type}, {"Qt 3", Int64.Type}, {"Code 4", Int64.Type}, {"Qt 4", type number}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Num", each Text.End([Attribute],1)),
#"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Type", each if Text.Contains([Attribute], "Code") then "Code" else if Text.Contains([Attribute], "Qt") then "Qt" else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Attribute"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ID", "Type", "Num", "Value"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Num", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Type]), "Type", "Value"),
#"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Num"})
in
#"Removed Columns1"
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.