Unpivot - [DataFormat.Error] We couldn't convert to Time

[filarap]

Hi All,

I am strugling with an error OLE DB or ODBC error: [DataFormat.Error] We couldn't convert to Time.

I already tried solutions from below, but it did not work

https://community.powerbi.com/t5/Desktop/DataFormat-error-We-couldnt-convert-to-Number/td-p/61492

https://community.powerbi.com/t5/Desktop/Error-DataFormat-Error-We-couldn-t-convert-to-Number-Detail...

I have interactions with duration, and what i am trying to achieve is to split this time into 30 min time buckets, which i suceeded, but had to create column for each bucket.

After i did it, i tried using unpivot in order to get data in format that i need for going forward.

However once i apply this, i get the error above. Below is query i made. Time buckets i had in format "08:00" as well, data type time, but same thing once apply.

State of query now:

Please help

Filarap

[d_gosbell]

Rather than fighting with pivot/unpivot it might be easier to look at this a different way. To round down a time to the nearest 30 minute boundary you could use a custom column like the following in your PowerQuery

Duration.From(Number.RoundDown(Duration.TotalMinutes([Duration])/30)/48)

where the 30 indicates the size of the buckets you want to create and the 48 is the number of those "buckets" in a day. So you could change these 2 numbers to 15 and 96 to round down to the closest 15 minute interval.

[filarap]

Thank you d_gosbell,

I already have 30 minute buckets created. Sorry dont have access to file at the moment, so i will replicate in table below.

Table below shows data before i did unpivot (Screenshot from initial post). 

Duration(min)	Starting Time stamp	Ending Time Stamp	Starting Bucket	Ending Bucket	15:00	15:30	16:00	16:30
48	15:32	16:20	15:30	16:00	0	28	20	0

Starting time stamp is taken from date/time cell from raw data.

I have create columns showing exact time spent in each interval. This was done using nested ifs.

No issue with apply before unpivot. As soon i do unpivot, and try to apply, i get error above. 

Or am I misunderstanding your formula? 

Regards

Filarap

[d_gosbell]

Oh I thought your raw data was different.

But i'm not able to replicate your error. I pasted your example data into a new Power BI report, selected all the columns that were not time buckets then chose the UnPivotOtherColumns transformation which produced the PowerQuery step below

Table.UnpivotOtherColumns(#"Changed Type1", {"Duration(min)", "Starting Time stamp", "Ending Time Stamp", "Starting Bucket", "Ending Bucket"}, "Attribute", "Value")

This unpivoted the example data without an error. Are you able to share some example data (does not have to be real data) which reproduces your issue.

ps. Sorry for the late reply it appears that the forum notifications have stopped working

[filarap · Accepted solution]

Thank you for the attempt.

I managed to find the issue. 
4 errors that happend in column that was supposed to be time value. Calculation in that column caused cells to be over 1, immediatelly preventing it to be converted to time. Once i fixed these errors, unpivot worked like a charm 🙂

I realy appreciate your asistance.

Regards

Filarap