Forum Discussion
Transpose table with every period in between dates
So i have a table with rental objects. Each object has an ID. each row in the table shows new index regulations. So for the chosen ID i got 2 regulations. In order to compare vs accounting i need to transpose this table into a monthly table for each object ID like this from "date start" to a set date. In this example desember.24.
| Object ID | Year | Month | Rent |
| ....c9d | 2020 | 03 | 5520 |
| ....c9d | 2020 | 04 | 5520 |
| ....c9d | 2020 | 05 | 5520 |
| ....c9d | ... | ... | ... |
| ....c9d | 2021 | 03 | 5912 |
| ....c9d | 2021 | 04 | 5912 |
| ....c9d | ... | ... | ... |
| ....c9d | 2022 | 03 | 6000 |
| ....c9d | 2022 | 04 | 6000 |
| ....c9d | 2022 | 05 | 6000 |
| ....c9d | .... | .... | .... |
| ....c9d | 2024 | 12 | 6000 |
And i wanna do this in power query in Power BI.
Hi stiani ,
if you add a new column calling this custom function: Date.DatesBetween to retrieve dates between 2 dates in Power BI and Power Query – The BIccountant
you should achieve your goal without transposing. When you choose "Month" in the 3rd parameter, you can simply expand the result and will get one row per month.
1 Reply
Hi stiani ,
if you add a new column calling this custom function: Date.DatesBetween to retrieve dates between 2 dates in Power BI and Power Query – The BIccountant
you should achieve your goal without transposing. When you choose "Month" in the 3rd parameter, you can simply expand the result and will get one row per month.