Forum Discussion
Assign date and time to values in columns
Hello everyone,
I've been working with power BI around 4 months, and now I'm starting to get more complicated tasks in my work. I have a large dataset with a date column and several values columns (see annex):
Each "DXX" column represents a time of date in which a value was measured (for example, column D00 means the value was measured at 00:00 from the day of DATE colum (DATE_VAL); column D01 means the value was measured at 00:30... and so each half an hour).
My question is: it is possible to arrange the existing DXX values by time in the date column? I guess some rows with the hour corresponding to each value must be added to the date column.
What I have:
| DATE | D00 | D01 | D02 | D03 |
| 01/01/2020 | 32 | 25 | 24 | 21 |
What I would like:
| DATE | VALUE |
| 01/01/2020 00:30 | 32 |
| 01/01/2020 01:00 | 25 |
| 01/01/2020 01:30 | 24 |
| 01/01/2020 02:00 | 21 |
Thank you so much in advance for your answers,
Cheers!!
Hi,
Please try to unpivot All 'Dxx' columns:
Then split the [Attribute] column:
After Apply&Close, please try to create a calculated column:
Column = FORMAT('Table'[Date]+TIME(0,'Table'[Attribute.2]*30,0),"YYYY-MM-DD HH:MM:SS")The result shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto Zhi
3 Replies
GordonliljSolution Sage
Hi,
Using the power query editor you could unpivot the DXX columns in order to achieve something like what you're after
- v-gizhi-msft
Community Support
Hi,
Please try to unpivot All 'Dxx' columns:
Then split the [Attribute] column:
After Apply&Close, please try to create a calculated column:
Column = FORMAT('Table'[Date]+TIME(0,'Table'[Attribute.2]*30,0),"YYYY-MM-DD HH:MM:SS")The result shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto Zhi