Hi Everyone,
For the moment my collegue made a table with a text/action in the first cell of the table, then a cycle of repeat and then "matrix"-styled columns of the years the action should take place with the price of it.
For example:
- Clean the roof - 2 years cycle - 2023 (10k) - 2024 (0k) - 2025 (10k) ...
The problem is that such structure not ideal is to make pivot table and graphs.
Have you any idea how to do it better? (I can use PowerQuery and i'm starting with Power Pivot if the options are in that way).
Cordially
Julien Vandamme
Will be easier to provide a solution if you provide a sample worksheet and a mocked up solution of what you desire. 8-10 records should be sufficient..
| Action | Price | Starting year | Cycle [year] | 2023 | 2024 | 2025 | 2026 | 2027 | 2028 | 2029 | 2030 | 2031 | 2032 |
| Clean roof | 1000 | 2024 | 2 | 1000 | 1000 | 1000 | 1000 | 1000 | |||||
| Repaint wooden windows | 8000 | 2026 | 6 | 8000 | 8000 | ||||||||
| Change carpet | 20000 | 2027 | 15 | 20000 |
Is this clear enough ?
(I should have done it the first time, sorry)
Take it up to Power Query, unpivot the year columns, to come up with "year due" and "amount" columns, then, from there you can load them to pivot tables and create the pivot tables you need.
Hi Jessica,
Is that solution also applicable in power BI afterwards?
The idea is that I have a dynamic table so that the graphs are dynamic too if the customer decide to change the starting years or cycle of the actions.
I don't know much about Power Bi except what I have seen on YouTube, but my understanding is Power Bi uses the same Power Query so I would think so; you may have to do those same transformations but you should just be able to copy the M Code and paste it in Bi. If I'm way off, someone who knows more than me in this area, please correct me.
