Add more years to a table

[Anonymous]

Hi everyone!

I have a table from SQL with some data by years/months that stops at December 2023.

I want to add more years with the same data as previous year, where Value is multiplied by 5%. 

Example for 2024, I need to have the same number of rows with the same data, except culmun Value which will be = Value * 5%.

Is it possible? Maybe in power query?

Thank you!

[johnt75 · Accepted solution]

In Power Query you could duplicate the query for 2023, switch to the transform ribbon and use the Standard section from the Number column section to add 1 to the year and multiply the value by 1.05. Then append the 2 queries.

[Anonymous]

Thank You, but maybe there is a more automated solution?

I will need at least 3 more years to replicate and append, this will have an impact on performance.

[johnt75]

You could do it in the SQL. Depending on the flavour of SQL you could take the query which currently pulls data for 2022 and use that to define a CTE. Then the query which returns data could be a union of the base data and as many copies as you need, adding values to the year column and changing the data values.