PQ repeat rows n times

[cr731]

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

Item	Times to repeat	Other columns
A	3	ABC
B	2	DEF

<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,

Item	Times to repeat	Other columns
A	3	ABC
A	3	ABC
A	3	ABC
B	2	DEF
B	2	DEF

<tbody>
</tbody>

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

 

[MarcelBeug]

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]

Thanks; works great

 

[Useful]

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]

Hello Marcel,

your code snippet has done so well:

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

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
4051234567890	1234567						3	5
4051234567890	1234567						3	5
4051234567890	1234567						3	5
4051234567890	1234567						3	5
4051234567890	1234567						3	5
4051234567891	1234568						1	3
4051234567891	1234568						1	3
4051234567891	1234568						1	3

<tbody>
</tbody>

 

[MrPowerQuery]

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

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"

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]

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