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