cancel
Showing results for
Did you mean:
Highlighted
Resolver III

## 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                | 1/1/17   | 100

I need it to be like :

customer   |   Date    | amount

1                | 1/1/17   | 100

1                | 1/2/17   | 100

1                | 1/3/17   | 100

1                | 1/4/17   | 100

Is it possible to do it with power bi?

Thanks,

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III

## Re: Duplicate rows with new values

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 )
)```

```New date =
DATE ( YEAR ( 'New Table'[Date] ), MONTH ( 'New Table'[Date] ) + 'New Table'[Value], DAY ( 'New Table'[Date] ) )```
Regards,
Zubair

8 REPLIES 8
Highlighted
Super User III

## Re: Duplicate rows with new values

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 )
)```

```New date =
DATE ( YEAR ( 'New Table'[Date] ), MONTH ( 'New Table'[Date] ) + 'New Table'[Value], DAY ( 'New Table'[Date] ) )```
Regards,
Zubair

Highlighted
Super User III

@Drors

Regards,
Zubair

Highlighted
Super User III

## Re: Duplicate rows with new values

HI @Drors

I added one more row for testing

Regards,
Zubair

Highlighted
Resolver III

## Re: Duplicate rows with new values

wow thank you it works!

Do you have any idea how to do it with a regular table and not calculated one, I think it will make my work difficault later..

and again, Thank you very much, I work on it all day

Highlighted
Super User III

## Re: Duplicate rows with new values

Hi @Drors

I will look into Power Query solution

@MarcelBeugcould you help?

Regards,
Zubair

Highlighted
Community Champion

## Re: Duplicate rows with new values

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"```
Specializing in Power Query Formula Language (M)
Highlighted
Resolver III

## Re: Duplicate rows with new values

@MarcelBeug

Thank you its working!

Highlighted
New Member

## : Duplicate rows with new values

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.

BR

Pawel

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021