Forum Discussion
Solved
Table column to rows
Hello wise Friends,
I have the following input table:
| Ref Conjunt | Codi ini | Codi Fi |
| Cj1 | ini | Fi |
| Cj2 | ini | Fi |
And would like to transform it in Power Query with the following output
| Ref Conjunt | Codi |
| Cj1 | Ini |
| Cj1 | Fi |
| Cj2 | ini |
| Cj2 | Fi |
The aim is to append this table to a previous one and add delimiter rows at the start and end of a collection of groups by Ref. Conjunt Column.
Although it might be very simple, I've tried my best by unpivoting in different ways, but with no success.
Any help would be appreciated.
Thanks, folks.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs4yVNJRyszLBJJumUqxOiAhIxShWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ref Conjunt" = _t, #"Codi ini" = _t, #"Codi Fi" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ref Conjunt", type text}, {"Codi ini", type text}, {"Codi Fi", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Ref Conjunt"}, "Attribute", "Codi"), #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Other Columns",{"Ref Conjunt", "Codi"}) in #"Removed Other Columns"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
2 Replies
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs4yVNJRyszLBJJumUqxOiAhIxShWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Ref Conjunt" = _t, #"Codi ini" = _t, #"Codi Fi" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ref Conjunt", type text}, {"Codi ini", type text}, {"Codi Fi", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Ref Conjunt"}, "Attribute", "Codi"), #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Other Columns",{"Ref Conjunt", "Codi"}) in #"Removed Other Columns"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
