PQ repeat rows n times

[cr731]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Times to repeat[/TD]
[TD]Other columns[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]3[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD]DEF[/TD]
[/TR]
</tbody>[/TABLE]


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,

[TABLE="width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Times to repeat[/TD]
[TD]Other columns[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]3[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]3[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]3[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD]DEF[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD]DEF[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]EAN
[/TD]
[TD]SKU
[/TD]
[TD]Spalte1
[/TD]
[TD]blablab
[/TD]
[TD]bla
[/TD]
[TD]blub
[/TD]
[TD]blubl
[/TD]
[TD]Anzahl der zu druckenden Stickers
[/TD]
[TD]Summe
[/TD]
[/TR]
[TR]
[TD]4051234567890[/TD]
[TD]1234567[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]4051234567890[/TD]
[TD]1234567[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]4051234567890[/TD]
[TD]1234567[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]4051234567890[/TD]
[TD]1234567[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]4051234567890[/TD]
[TD]1234567[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4051234567891[/TD]
[TD]1234568[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4051234567891[/TD]
[TD]1234568[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4051234567891[/TD]
[TD]1234568[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[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