Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Drors
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
Zubair_Muhammad
Community Champion
Community Champion

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

Please try my custom visuals

View solution in original post

9 REPLIES 9
Zubair_Muhammad
Community Champion
Community Champion

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

Please try my custom visuals
Anonymous
Not applicable

how about if i need it dublicated until todays month? i dont need it dublicated only 11 times!

thanks,

@Drors

 

1004.png


Regards
Zubair

Please try my custom visuals

HI @Drors

 

Please see the file here

(With your sample data)

I added one more row for testing

 


Regards
Zubair

Please try my custom visuals

@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

Hi @Drors

 

I will look into Power Query solution

 

@MarcelBeugcould you help?

 

 

 


Regards
Zubair

Please try my custom visuals

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)

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

 

@MarcelBeug

Thank you its working!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.