Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 columns with the date in all the cells of the 3td column
I can do it with vba but i need to use power query ( no dax)
Thank you in advance
Solved! Go to Solution.
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
Split
with 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
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
Split
with this result
I hope this is helpful
Check out the July 2025 Power BI update to learn about new features.