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

[Anany]

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_id

Store_Name

city

INVOICE_DATE

Sales_Amount

AOP_ADT

S001	A	MUMBAI	4/16/2022	157507.18
S001	A	MUMBAI	4/17/2022	132927.16
S001	A	MUMBAI	4/18/2022	134101.77
S001	A	MUMBAI	4/19/2022	137277.19
S001	A	MUMBAI	4/20/2022	143295.75
S001	A	MUMBAI	4/21/2022	44236.33
S002	C	MUMBAI	1/1/2020	159357.66	277292

Expected Output-

store_id

Store_Name

city

INVOICE_DATE

Sales_Amount

AOP_ADT

S001	A	MUMBAI	4/16/2022	157507.18
S001	A	MUMBAI	4/17/2022	132927.16
S001	A	MUMBAI	4/18/2022	134101.77
S001	A	MUMBAI	4/19/2022	137277.19
S001	A	MUMBAI	4/20/2022	143295.75
S001	A	MUMBAI	4/21/2022	44236.33
S001	A	MUMBAI	4/21/2022	44236.33
S001	A	MUMBAI	4/21/2022	44236.33
S001	A	MUMBAI	4/21/2022	44236.33
S001	A	MUMBAI	4/21/2022	44236.33
S002	C	MUMBAI	1/1/2020	159357.66	277292

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

[MFelix · Accepted solution]

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

• 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:

• Disable the load from the AppendQuery

See result attach.

[Anany]

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