Duplicating rows with udpated data in Power Query

[mterry]

I'm trying to create a dataflow with power query for currency conversion rates. I have an excel file that has the rates I need, the issue is that the rates for the current month aren't updated until the end of the month and sometimes they're needed before that. What I'm trying to do is to use the rates for the latest month until they actually get finalized. So I'd like to duplicate all rows for the last month in the file, update the month columns by one month and keep the rates the same. Here's a sample of the data now:

Currency Code	FX	Date_EoM	Date_BoM
AED	0.25178093099999999	6/30/2024	6/1/2024
ANG	0.51622786899999995	6/30/2024	6/1/2024
ARS	0.0010788340000000001	6/30/2024	6/1/2024
AUD	0.60849902899999997	6/30/2024	6/1/2024
AWG	0.51657286899999999	6/30/2024	6/1/2024
BGN	0.51129199999999997	6/30/2024	6/1/2024

And here's what I would like it to look like:

Currency Code	FX	Date_EoM	Date_BoM
AED	0.25178093099999999	6/30/2024	6/1/2024
ANG	0.51622786899999995	6/30/2024	6/1/2024
ARS	0.0010788340000000001	6/30/2024	6/1/2024
AUD	0.60849902899999997	6/30/2024	6/1/2024
AWG	0.51657286899999999	6/30/2024	6/1/2024
BGN	0.51129199999999997	6/30/2024	6/1/2024
AED	0.25178093099999999	7/30/2024	7/1/2024
ANG	0.51622786899999995	7/30/2024	7/1/2024
ARS	0.0010788340000000001	7/30/2024	7/1/2024
AUD	0.60849902899999997	7/30/2024	7/1/2024
AWG	0.51657286899999999	7/30/2024	7/1/2024
BGN	0.51129199999999997	7/30/2024	7/1/2024

[Anonymous · Accepted solution]

Hi @mterry ,

Thanks for the reply from @aj1973 , please allow me to provide another insight: 

1. Filter last month’s data in power query.

2. Copy the current table and modify the column names of the two columns of dates.

3. Create custom column.

4. Remove unnecessary columns.

5. Append the copied table to the original table.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mterry]

Thank you that's exactly what I needed. 

[aj1973]

Hi @mterry 

With Dataflows gen 2 you can achieve it 

https://learn.microsoft.com/en-us/fabric/data-factory/dataflow-gen2-data-destinations-and-managed-se...

Incremental refresh can do it too

Other easy option is to add a new colonne in your Excel file to insert a Timestamp for changes.

[mterry]

Bumping this back up as the response doesn't solve my issue. 

[mterry]

I'm not certain that link helps with the specific problem unless I missed something? I don't need a timestamp for changes, I'm hoping to copy some of the existing rows and append them to the existing table with an updated date.