Forum Discussion
Duplicating rows with udpated data in Power Query
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 |
Hi Anonymous ,
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.
5 Replies
Hi Anonymous ,
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.
- aj1973
Community Champion
Hi Anonymous
With Dataflows gen 2 you can achieve it
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.
