Forum Discussion
Transformed table with new rows
Good day everyone.
I need to make transformed table using data from next table:
| IDLE_ID | START_DT | START_TIME | END_DT | END_TIME | IDLE_TYPE |
| 122851 | 09.09.2019 | 05:57:00 | 01.10.2019 | 09:00:00 | Planned |
| 123111 | 01.10.2019 | 09:30:00 | 02.10.2019 | 17:00:00 | Planned |
I need to make visualization like this (abridged example):
| IDLE_ID | START_DT | START_TIME | END_DT | END_TIME | IDLE_TYPE |
| 122851 | 09.09.2019 | 05:57:00 | 09.09.2019 | 24:00:00 | Planned |
| 122851 | 10.09.2019 | 00:00:00 | 10.09.2019 | 24:00:00 | Planned |
| 122851 | 11.09.2019 | 00:00:00 | 11.09.2019 | 24:00:00 | Planned |
| 122851 | 12.09.2019 | 00:00:00 | 12.09.2019 | 24:00:00 | Planned |
| 122851 | 13.09.2019 | 00:00:00 | 13.09.2019 | 24:00:00 | Planned |
| 122851 | 14.09.2019 | 00:00:00 | 14.09.2019 | 24:00:00 | Planned |
| 122851 | 15.09.2019 | 00:00:00 | 15.09.2019 | 24:00:00 | Planned |
| 122851 | 25.09.2019 | 00:00:00 | 25.09.2019 | 24:00:00 | Planned |
| 122851 | 26.09.2019 | 00:00:00 | 26.09.2019 | 24:00:00 | Planned |
| 122851 | 27.09.2019 | 00:00:00 | 27.09.2019 | 24:00:00 | Planned |
| 122851 | 28.09.2019 | 00:00:00 | 28.09.2019 | 24:00:00 | Planned |
| 122851 | 29.09.2019 | 00:00:00 | 29.09.2019 | 24:00:00 | Planned |
| 122851 | 30.09.2019 | 00:00:00 | 30.09.2019 | 24:00:00 | Planned |
| 122851 | 01.10.2019 | 00:00:00 | 01.10.2019 | 09:00:00 | Planned |
| 123111 | 01.10.2019 | 09:30:00 | 01.10.2019 | 24:00:00 | Planned |
| 123111 | 02.10.2019 | 00:00:00 | 02.10.2019 | 17:00:00 | Planned |
I know, it looks really strange, but i need this visualization for easy count of idle for each day. Also first table was formed in DirectQuery, and i need not to change initial table.
Any suggestions come useful.
Best regards
Alexandr
2 Replies
Anonymous , Not very clear. But refer if my blog can help
#POWERBI: How to divide/distribute values between start date or end date or count days across months/days: https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785
This is link to .pbix example of data from my existing table
As you can see in .pbix file, i understood how to use your method, but main problem is that i can't use measure day for count of idle time.
Please check correctness of my actions in this file.
Best regards, Alexandr
