Create duplicate Rows based on cell value

cidfidou

Board Regular
Joined
Jan 19, 2009
Messages
163
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
 
Last edited:

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

MarcelBeug

Well-known Member
Joined
Apr 25, 2014
Messages
1,811
In this video you can see how to create the following code:
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

Board Regular
Joined
Jan 19, 2009
Messages
163
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
 
Last edited:

macfuller

Active Member
Joined
Apr 30, 2014
Messages
274
Office Version
365
Platform
Windows
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,340
Messages
5,510,732
Members
408,809
Latest member
Matthiasek

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top