Search code examples
powerqueryrepeatcalculated-columns

create column that repeats the values of another column power query

I have a table in power query and I'd like all the columns to have the same amount of rows. So I'd like to create a new column which repeats the exact sequence I have in too tiny column.

enter image description here

So the first rows of my column contains 24 numbers and the rest is null, and I'd like to repeat the exact sequence of 24 numbers 24 times. I've seen there is a fill down function but it fills my columns only with the last number of my sequence.

Can someone help me out on that thing ?

Solution

  • Is this what you are looking for? It takes the non-null contents of Column1 and repeats it 24 times

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
x= Table.FromList(List.Transform(List.Repeat(List.RemoveNulls(Source[Column1]),24), each Text.From(_)))
in x

    enter image description here

    you could repeat your data 24 times, then combine that column in with other data, as in below

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
x=List.RemoveNulls(Source[Column1]),
y= {List.Transform(List.Repeat(x,List.Count(x)), each Text.From(_))},
Source2 = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
custom1 = Table.ToColumns(Source2) & y,
custom2 = Table.FromColumns(custom1,Table.ColumnNames(Source2) & {"Newcolumn"})
in  custom2

    enter image description here