Error (decimals) with changing type of data from "time" to text"

Power Query
1

Hi all,
First time I have encountered the problem when I am changing type of data from " time" to text , I am getting decimals.
image
This is a part of the code regarding this problem :
let
Source = Excel.Workbook(File.Contents(“C:\Users\iwona \Desktop\Temp Agency - PBI .xlsx”), null, true),
Sheet1_Sheet = Source{[Item=“Sheet1”,Kind=“Sheet”]}[Data],
#“Promoted Headers” = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#“Changed Type” = Table.TransformColumnTypes(#“Promoted Headers”,{{“Depot”, Int64.Type}, {“Operational Date”, type date}, {“Employee”, Int64.Type}, {“Pay Value”, Int64.Type}, {“Designation”, type text}, {“end_time”, type number}, {“start_time”, type number}, {“Worksheet”, type text}, {“Basic Hours”, type number}}),
#“Duplicated Column” = Table.DuplicateColumn(#“Changed Type”, “end_time”, “end_time_asTime”),
#“Changed Type1” = Table.TransformColumnTypes(#“Duplicated Column”,{{“end_time_asTime”, type text}}),

I always do the same steps and have no idea what is the problem here.

Would be grateful for help .
Many thanks,

Iwona

2

Hi @Iwona,

You haven’t provided a sample and know that your locale can also be a factor. Give this a go:
Create this function:

image

and invoke that over your “time” colulmn

image
image782×97 4.55 KB

Let me know how it goes…

3

hi @Melissa

Please find attached the PBI file for this case
Activity table - forum.pbix (40.8 KB)

image
image1504×538 46.5 KB

Grateful for your help and input.

Iwona

4

Hi @Iwona,

Can you provide the source file as well?
C:\Users\iwona.xxxxxxxxxx\Desktop\Forum - calc.xlsx

Can’t work on this without that, I’m afraid.- Thanks so much.

5

@Melissa ,
This is my source of data for this PBI -
Forum - calc.xlsx (32.7 KB)

Many thanks, Iwona

6

Hi @Iwona,

Seems you are bringing in numbers, give this a go:
Number.Round([#"end_time"], 2)

before you convert to text

image
image1157×275 19.3 KB

I hope this is helpful

7

Hi @Melissa

I am getting errors in 11 rows and don’t know where they come from :

image
image1144×288 13 KB

Could you please help .

Many thanks,

Iwona

8

What does the error message say?
Also from the image it only seems to happen for whole numbers, can you confirm that? And what time does “1” represent?

9

@Melissa
It doesn’t say much:
image
Forum - calc.xlsx (32.7 KB)

Activity table - forum.pbix (40.8 KB)

Could I kindly request two calculations regarding ‘start time’ and ‘end time’ for each employee :

  1. duration of work shift expressed in " hh:mm" ( they sometimes finish after midnight and start before midnight )
  2. and the same duration expressed in decimals

Could you please help.
Will be grateful

Iwona

10

Hi @Iwona,

Note that the supplied xlsx was slightly different to the data in the pbix.
After applying some brute force with this custom function

image
.

You can Group and retrieved first StartTime and last EndTime before performing this simple calc. To return the time value that matches your expected output.

image
image1161×505 38.1 KB

Here’s your sample file.
Activity table - forum (1).pbix (34.0 KB)

I hope this is helpful

11

Hi @Melissa

I need to create visualization with these two nee calculations but I can’t see here durations in “hh:mm” and duration in decimals:
image

I don’t know what to do, Please help :pray: :pray: :pray:

Many thanks,

Iwona

12

Hi @Iwona,

I’ve disabled load for my query, just go to the Power Query Editor and enable load to bring the data in. Okay. Note that you’ll have to switch the selected item in the FileLocation parameter.
Hope that helps.

14

Hi @Iwona, did the response provided by @Melissa help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.