Forum Discussion

Drors's avatar
Drors
Resolver III
8 years ago
Solved

Duplicate rows with new values

Hi,   I have a table like that :  Customer|Date|amount   I need to duplicate each row 12 times each time with the next month  example: original row customer   |   Date    | amount 1          ...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi Drors

     

    Try this solution

     

    Go to Modelling Tab>>> New Table

     

    New Table =
GENERATE (
    TableName,
    VAR mymonth = MONTH ( TableName[Date] ) RETURN GENERATESERIES ( 0, 11, 1 )
)

    Now Add this CALCULATED COLUMN to your NEW TABLE

     

    New date =
DATE ( YEAR ( 'New Table'[Date] ), MONTH ( 'New Table'[Date] ) + 'New Table'[Value], DAY ( 'New Table'[Date] ) )
MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

This would be my Power Query solution:

 

let
    Source = TableName,
    DateList = Table.TransformColumns(Source,{{"Date", (startdate) => List.Transform({0..11}, each Date.AddMonths(startdate,_)), type {date}}}),
    #"Expanded Date" = Table.ExpandListColumn(DateList, "Date")
in
    #"Expanded Date"
  • Drors's avatar
    Drors
    Resolver III
    8 years ago

    MarcelBeug

    Thank you its working!

  • pawel470's avatar
    pawel470
    New Member
    6 years ago

    Hi MarcelBeug , superb solution.

     

    Is there a chance to change number of new rows dynamicly based on value from column, differently for every row? So the same rows needs to be duplicated 2 and some 12 times depending what value is in columnt QTY in row that is duplicated. Can I ask for an example in m-language?

     

    2nd question is about changing values in 2 kolumns for each duplicated row. How to change date adding 1 month (as in the example) and in the same time add 1 day for date_2. 

     

    Thank you in advance

    BR

    Pawel