Search code examples
powerquery

Power Query copy column based on value

I have a table with different columns and values. In one row is the amount of copies for the columns.

enter image description here

At the end of the transformation, i want to have a table which looks like this: The amount of copies create copies of the columns plus the index number.

enter image description here

I did nothing try so far, because i have no idea where to start and the web search did not get me the right answers.

Solution

  • You can try this pasted into home..advanced editor ... using the real reference to your data in the first row

    let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
x=Table.ToColumns(Source),
r = List.Positions (x),
repeats={1}&List.RemoveFirstN(Record.FieldValues(Source{0}),1),
Values = List.Combine(List.Transform(r, each List.Repeat({x{_}},repeats{_}))),
Titles = List.Combine(List.Transform(r, each let a=repeats{_}, b = Table.ColumnNames(Source){_}, c = List.Transform({1..a}, each b&Text.From(_)) in c)),
expand = Table.FromColumns(Values,Titles)
in expand

    enter image description here

    Additional code snippets:

    If you want a space between number and text use

    b&" " &Text.From

    If you don't want a number associated with a column that only appears once, then use

    {1..a}, each if a=1 then b else b&Text.From