topic Re: Add more years to a table in DAX Commands and Tips

Add more years to a table

Anonymous — Wed, 01 Feb 2023 11:32:16 GMT

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!

Re: Add more years to a table

johnt75 — Wed, 01 Feb 2023 11:46:06 GMT

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.

Re: Add more years to a table

Anonymous — Wed, 01 Feb 2023 14:08:07 GMT

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.

Re: Add more years to a table

johnt75 — Wed, 01 Feb 2023 14:13:01 GMT

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.