cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anany
Helper I
Helper I

Duplicate Rows mutliple times basesd on Max Date for each Store ID

Hi,

Hope you're safe and doing well!

I'm trying to duplicate the max date row 4 times for each store ID.

For example- 

Max date for Store name A is 4/21/2022. Now I want to duplicate this row 3 more times and this should work for each Store Name.
Input-

store_idStore_NamecityINVOICE_DATESales_AmountAOP_ADT
S001AMUMBAI4/16/2022 157507.18 
S001AMUMBAI4/17/2022 132927.16 
S001AMUMBAI4/18/2022 134101.77 
S001AMUMBAI4/19/2022 137277.19 
S001AMUMBAI4/20/2022 143295.75 
S001AMUMBAI4/21/2022 44236.33 
S002CMUMBAI1/1/2020159357.66277292

 

Expected Output-

store_idStore_NamecityINVOICE_DATESales_AmountAOP_ADT
S001AMUMBAI4/16/2022 157507.18 
S001AMUMBAI4/17/2022 132927.16 
S001AMUMBAI4/18/2022 134101.77 
S001AMUMBAI4/19/2022 137277.19 
S001AMUMBAI4/20/2022 143295.75 
S001AMUMBAI4/21/2022 44236.33 
S001AMUMBAI4/21/202244236.33 
S001AMUMBAI4/21/202244236.33 
S001AMUMBAI4/21/202244236.33 
S001AMUMBAI4/21/202244236.33 
S002CMUMBAI1/1/2020159357.66277292

 

Attached below are the sample data and the pbix file. 
@amitchandakwill highly appreciate it if anyone can help me with this.

 

Thanks!

https://drive.google.com/drive/folders/1BKREgrynNKXWCWRBNruO8AXkg86L-Zgz?usp=sharing

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @Anany ,

 

In this case you need to use Power query and do the following:

 

Duplicate the Query that you have and then add the following steps after your transformations:

  • Group by:
    • store_id
    • Max of Invoice date and all rows

MFelix_0-1651273650533.png

 

  • Add a custom column with the following code:
= Table.Max([ALL ROWS], "Index.INVOICE_DATE")
  • Expand the custom column (be aware to not select the store and the invoice date
  • Remove column ALL ROWS
  • On the other query append the previous query 4 times:

MFelix_1-1651273800172.png

  • Disable the load from the AppendQuery

See result attach.

MFelix_2-1651273916301.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

Hi @Anany ,

 

In this case you need to use Power query and do the following:

 

Duplicate the Query that you have and then add the following steps after your transformations:

  • Group by:
    • store_id
    • Max of Invoice date and all rows

MFelix_0-1651273650533.png

 

  • Add a custom column with the following code:
= Table.Max([ALL ROWS], "Index.INVOICE_DATE")
  • Expand the custom column (be aware to not select the store and the invoice date
  • Remove column ALL ROWS
  • On the other query append the previous query 4 times:

MFelix_1-1651273800172.png

  • Disable the load from the AppendQuery

See result attach.

MFelix_2-1651273916301.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thank you so much !! It's working perfectly fine.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors