cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver III
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
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 )
)

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] ) )
Regards,
Zubair


View solution in original post

8 REPLIES 8
Highlighted
Super User III
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 )
)

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] ) )
Regards,
Zubair


View solution in original post

Highlighted
Super User III
Super User III

Re: Duplicate rows with new values

@Drors

 

1004.png

Regards,
Zubair


Highlighted
Super User III
Super User III

Re: Duplicate rows with new values

HI @Drors

 

Please see the file here

(With your sample data)

I added one more row for testing

 

Regards,
Zubair


Highlighted
Resolver III
Resolver III

Re: Duplicate rows with new values

@Zubair_Muhammad

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
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
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
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. 

 

Thank you in advance

BR

Pawel

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

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

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors