Assign date and time to values in columns

[Anonymous]

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!! 

[v-gizhi-msft · Accepted solution]

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:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[Anonymous]

I'ts perfect! Thank you so much for your help!! 

[Gordonlilj]

Hi,

Using the power query editor you could unpivot the DXX columns in order to achieve something like what you're after