Forum Discussion
Solved
Transform an horizontal table in vertical (3 columns) with Power Query
Hi to everyone, I have a table like the one in the image above, every year there will be 3 new columns (name, description and a date), i need to trasform this table like the one below, only 3 column...
Hi LukeReds
Give this a go. It assumes the same set of three columns is repeated in the exact same order.
let Source = YourTable, Split = Table.Combine( List.Transform( List.Split( Table.ColumnNames(Source), 3 ), each [ t = Table.SelectColumns(Source, _), v = List.Last( Table.ColumnNames(t)), r = Table.FromColumns( List.RemoveLastN( Table.ToColumns(t), 1) & {List.Repeat( {v}, Table.RowCount(t))}, {"Name", "Description", "Date"} ) ][r] ) ) in Splitwith this result
I hope this is helpful
Hi LukeReds, 2 more solutions:
Result
v1
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKi7JT842VNJRSkktTi5SgHOBCKdUrA5UnxGqpBGSPmxScH3GqJLGSPqwScH1maBKmiDpwyYF12eKKmmKpA+bVGwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, description = _t, #"01/01/2000" = _t, name2 = _t, description2 = _t, #"01/01/2001" = _t]),
ToCol = List.TransformMany(Table.ToRows(Source),
each {List.Alternate(_, 1,2,2)},
(x,y)=> {y{0}, y{1}, Table.ColumnNames(Source){2}, y{2}, y{3}, Table.ColumnNames(Source){5}} ),
ToTbl = Table.Combine(List.Transform(List.Split(List.Zip(ToCol), 3), (x)=> Table.FromColumns(x, {"name", "description", "date"})))
in
ToTbl
v2 - with this solution it doesn't matter how many pairs of columns do you have (i.e. you can have 5 pars of [Name], [Description] and [Date] columns)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKi7JT842VNJRSkktTi5SgHOBCKdUrA5UnxGqpBGSPmxScH3GqJLGSPqwScH1maBKmiDpwyYF12eKKmmKpA+bVGwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, description = _t, #"01/01/2000" = _t, name2 = _t, description2 = _t, #"01/01/2001" = _t]),
Ad_Helper = Table.AddColumn(Source, "Helper", each
[ a = Record.ToList(_),
b = List.Alternate(a, 1, 2, 2), //name and description
c = List.Alternate(Record.FieldNames(_), 2, 1), //every 3rd column name
d = List.Split(b, 2), //splitted name and description
e = List.Transform({ 0..List.Count(c)-1 }, (x)=> d{x} & {c{x}}) //combined name, descriptotion, 3rd column name
][e], type list),
Helper = Table.Combine(List.Transform(List.Zip(Ad_Helper[Helper]), (x)=> Table.FromRows(x, {"name", "description", "date"})))
in
Helper
