Unpivot columns with a time heading error

[rpinxt]

So we have an excel file like :

So we have times (manually) on the first row and names on the rows below.

I want to use this data in Power Bi so I loaded the file :

Then I use the first row as header, but you can already see it manufactures its own date before it and not just the time.

So now I wanted to unpivot and at first this looks to be promising:

Exactly what we want, flat data and names for the time slots.

But of course we want first column to be time so I wanted to change the type to 'Time' :

Error......

Anybody who knows how we can pull this off?

[rpinxt · Accepted solution]

Well I have found a workaround...

So if I set the first column as Date/Time I get a valid Date/Time field (no error).

Now with a custom column I can derive the time from that, as can been seen, via this M code :

Would be ok I guess. Still more steps in the editor.

Still wondering if there is no directer solution.

[Anonymous · Accepted solution]

Hi  @rpinxt ,

I'm glad you found a solution, another way is to select Parse under the time tab.

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[rpinxt · Accepted solution]

Thanks @Anonymous this is indeed a quite similar solution where an extra column is added.

But in the mail I received you also mentioned to just close the editor with the date/time setting and then in normal view change the data time to time.

And that works also! Strange as in query editor this resulted in an error.

[rpinxt · Accepted solution]

Well I have found a workaround...

So if I set the first column as Date/Time I get a valid Date/Time field (no error).

Now with a custom column I can derive the time from that, as can been seen, via this M code :

Would be ok I guess. Still more steps in the editor.

Still wondering if there is no directer solution.

[Anonymous · Accepted solution]

Hi  @rpinxt ,

I'm glad you found a solution, another way is to select Parse under the time tab.

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.