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
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Solution Authors