# PQ repeat rows n times



## cr731 (Nov 21, 2016)

In Power Query, if I have a table list this,


ItemTimes to repeatOther columnsA3ABCB2DEF

<tbody>

</tbody>

I want to repeat each Item by the number of times in the 'Times to repeat' column, with every other column just repeating the same values.

So given the above, I want an output like this, 


ItemTimes to repeatOther columnsA3ABCA3ABCA3ABCB2DEFB2DEF

<tbody>

</tbody>
Can I use List.Generate or Table.Repeat for this?  Or what's the best way?


----------



## MarcelBeug (Nov 21, 2016)

It can easily be done with List.Numbers.
With PQ in Excel:

```
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Times", Int64.Type}, {"Other", type text}}),
    Repeated = Table.TransformColumns(Typed, {"Times", each List.Numbers(_,_,0), type list}),
    Expanded = Table.ExpandListColumn(Repeated, "Times")
in
    Expanded
```


----------



## cr731 (Nov 21, 2016)

Thanks; works great


----------



## Useful (Dec 7, 2016)

Hello,

also


```
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each {1..[Times to repeat]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Custom"})
in
    #"Removed Columns"
```


----------



## MrPowerQuery (Aug 13, 2018)

Hello Marcel,

your code snippet has done so well:



MarcelBeug said:


> It can easily be done with List.Numbers.
> With PQ in Excel:
> 
> ```
> ...



But now i need a single empty row after each changing EAN Number to seperate them from each other.

So my code in Power Query M language is:

```
[FONT=Calibri]let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each {1..[Summe]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Custom"})
in
    #"Removed Columns"
[/FONT]
```

Now i want to add one empty row after every new data. The Result should look like this:
Somehow a Table.AddRows() function should do the trick. But i just dont know the parameters to put in to fit my needs.


*EAN
**SKU
**Spalte1
**blablab
**bla
**blub
**blubl
**Anzahl der zu druckenden Stickers
**Summe
*40512345678901234567354051234567890123456735405123456789012345673540512345678901234567354051234567890123456735405123456789112345681340512345678911234568134051234567891123456813

<tbody>

</tbody>


----------



## MrPowerQuery (Aug 13, 2018)

Ah iam sorry it was "Useful" who replied this:



Useful said:


> Hello,
> 
> also
> 
> ...



*I just finished this with a macro:*

```
Public Sub Leere_Zeile_bei_Wechsel() 
    Dim lngRow As Long 
    Application.ScreenUpdating = False 
    For lngRow = Cells(Rows.Count, 1).End(xlUp).Row To 4 Step -1 
        If Cells(lngRow, 1).Value <> Cells(lngRow - 1, 1).Value And _ 
            Not IsEmpty(Cells(lngRow, 1)) And Not IsEmpty(Cells(lngRow - 1, 1)) Then _ 
            Rows(lngRow).Insert Shift:=xlShiftDown 
    Next 
    Application.ScreenUpdating = True 
End Sub
```

Hope this will be helpful for somebody else.

greetz


----------



## VBA Geek (Aug 14, 2018)

MrPowerQuery said:


> Now i want to add one empty row after every new data. The Result should look like this:
> Somehow a Table.AddRows() function should do the trick. But i just dont know the parameters to put in to fit my needs.



This  should help you with doing it via PowerQuery


----------

