Convert Field of Time Type During Data Refresh

[ovetteabejuela]

Hi,

I just wanted to know how to do this during "Get Data".

What I am doing:

1. Upload this table to PowerBI:

date_trans	dur_scheduled
12/1/2016	9:00
12/2/2016	9:00
12/3/2016	0:00
12/4/2016	0:00
12/5/2016	9:00
12/6/2016	9:00
12/7/2016	9:00
12/8/2016	9:00
12/9/2016	9:00
12/10/2016	0:00

2. I wanted to do an aggregate so I did (and I have no problem with the code, it's aggregating as expected):

Duration Schedule Aggregate = SUMX(tablename,tablename[dur_scheduled]) * 24

3. Basically I don't want to do this for every field that is of time type so the question is:

Can I do this during "Edit Queries" --- multiply the time by 24 the same way as the code above.\

Thank you.

[Anonymous · Accepted solution]

Hi @ovetteabejuela，

If you mean that multiply the time field by 24 before loading data into Power BI, from my point of view, you would need to implement the process in  your source.

However, if you want to implement the multiplication for multiple time type field in Query Editor, to simplify the process,  you can create a parameter in Query Editor as shown in the first screenshot, change the type of your time field to Decimal number, then add a custom column as shown in the second screenshot.

The above steps generate bold part of the following code in Advanced Editor, you can add these codes (replace dur_scheduled with other field names) into Advanced Editor of different queries based on your needs.

let
    Source = Excel.Workbook(File.Contents("path\New Microsoft Excel Worksheet (3).xlsx"), null, true),
    Table2_Table = Source{[Item="Table2",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table2_Table,{{"dur_scheduled", type time}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"dur_scheduled", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each [dur_scheduled]*Para),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type number}})
in
    #"Changed Type2"

Thanks,
Lydia Zhang

[ovetteabejuela]

Spoiler

"If you mean that multiply the time field by 24 before loading data into Power BI, from my point of view, you would need to implement the process in  your source."

 We'll this is actually an option an I think you mean to pass on the workload to the source, but what I was trying to get rid of is additional manual interference on the existing process.. I will also revisit the process on acquiring the data.

Thanks a lot for the help @Anonymous

[EDIT] ...and of course your solution works! thanks.