Create duplicate Rows based on cell value

[cidfidou]

Hi Guys,

I am trying to find a way to duplicate records based on another record value (in power query)

example:

Location Value
loc1 2
loc2 3

Result

loc1
loc1
loc2
loc2
loc2


Thanks in advance

 

[MarcelBeug]

In this video you can see how to create the following code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {1..[Value]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Value", "Custom"})
in
    #"Removed Columns

 

[cidfidou]

Hi Marcel,

What an amazing answer with video on top of it!!! Thank you so much for your valuable time. You are my new hero.

I will defo spemd some time on your youtube channel

 

[macfuller]

Marcel -

I just saw this answer. Is there a way I could duplicate rows based on dates? I receive data with a start date for a weekly production volume. I'm having trouble figuring out in DAX how I can write a measure to show that weekly data in a pivot table - that production is happening in the next week but there's no record of it. So I'm wondering if the answer is to duplicate the weekly data in the original query.

So for a production facility with a lifespan of (say) 5 years, I would like to duplicate the rows incrementing the week on each row up until the 5 year mark. If I add production in year 2 then I would only want 4 * 52 rows of data, and so on.