Recognise Column as a Time?

[CalvinL]

Hi all,

First time using Power BI, so apologies if it sounds confusing/doesn't make sense or isn't possible.

Basically, I've got a dataset that records values every 30 mins and stores them under columns "half hour 1" to "half hour 48". What I'm trying to do is to use these columns to be able to drill down by every hour and half hour. Is there a way to have it recognise "half hour 1" as "00:30", "half hour 2" as "01:00" etc. ?

Or is there an easier way to do this to drill down by every hour/half hour? There's no timestamps in the data, only dates.

All help appreciated, thanks.

[Greg_Deckler]

I think this will help: https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

[parry2k]

@CalvinL paste sample data, what column identifies the time?

[CalvinL]

Date		Half Hour 1	Half Hour 2	Half Hour 3	Half Hour 4	Half Hour 5	Half Hour 6	Half Hour 7	Half Hour 8	Half Hour 9
01/01/2019		87	80	67	81	82	84	77	78	65
20/04/2019		89	90	92	67	78	85	88	86	71
10/05/2019		90	88	76	78	75	86	82	81	82
15/03/2019		88	75	90	91	77	72	81	85	88
07/02/2019		90	92	78	65	84	77	91	84	72

Sample data above^

[camargos88]

Hi @CalvinL ,

Try this m code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZDRDcAgCER38buJQFV0FuP+a5TDiGmihwq8nMyZiLMtIR7pSV0hZNL8xBCBFBPFm3ZkqwmSO65nJqFMJTiQAc6QA/PGjobup4Y3MFBRPILDxqmH4wgv13YQWk/3dhYe39jOqZne8BON2xTHby4inJXwBA5pJvn58S/dMdzROHZfJWaDuNYH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, #"Half Hour 1" = _t, #"Half Hour 2" = _t, #"Half Hour 3" = _t, #"Half Hour 4" = _t, #"Half Hour 5" = _t, #"Half Hour 6" = _t, #"Half Hour 7" = _t, #"Half Hour 8" = _t, #"Half Hour 9" = _t, #"Half Hour 10" = _t, #"Half Hour 11" = _t, #"Half Hour 48" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Half Hour 1", Int64.Type}, {"Half Hour 2", Int64.Type}, {"Half Hour 3", Int64.Type}, {"Half Hour 4", Int64.Type}, {"Half Hour 5", Int64.Type}, {"Half Hour 6", Int64.Type}, {"Half Hour 7", Int64.Type}, {"Half Hour 8", Int64.Type}, {"Half Hour 9", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each Duration.ToText(#duration(0, 0, 30, 0) * Number.FromText(Text.Split([Attribute], " "){2}))),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type duration}, {"Value", Int64.Type}, {"Date", type datetime}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each [Date] + [Custom]),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", type datetime}})
in
#"Changed Type2"

Did I answer your question? Mark my post as a solution!
Ricardo

[CalvinL]

Hi @camargos88 ,

That didn't seem to work for me however, I think we're on the right track. One of my friends recommended trying to transform the column and assign time to the columns. Any idea how I can go about doing that?

[camargos88]

Hi @CalvinL ,

Did u get any error ?

Ricardo

[CalvinL]

@camargos88 Appreciate the reply. Unfortunately, that didn't work for me. I found another way around it though. I went into my data set and unpivoted the set so that there's a half hour 1, half hour 2 etc. value for each date. Now my problem is changing all the times in the date column to match the half hour 1, 2 etc.

Sample data (what it looks like now):

Date	Attribute	Value
01/01/2020 00:00:00	Half hour 1	10
01/01/2020 00:00:00	Half hour 2	12
01/01/2020 00:00:00	Half hour 3	15
01/01/2020 00:00:00	Half hour 4	16

[camargos88]

@CalvinL , 

The m code does it.

Check this file: Download PBIX 

Did I answer your question? Mark my post as a solution!
Ricardo

[CalvinL]

@camargos88 

Thank you for the file. So that custom column works, but only goes as far as 04:30:00 for some reason and then just repeats from there. Any ideas as to why it does that?

Also, would there be a solution to just increment the time in the date column rather than creating a new separate column for this?

[camargos88]

Hi @CalvinL ,

The formula to transform get the last character and transform it to hour like

Hour 4 = The formula gets the last character and multiply it by 30.

Duration.ToText(#duration(0,0, Number.FromText(Text.End([Attribute], 1)) * 30, 0))

The last value is Hour 9, so it is 04:30 starting from 00:00.

If you need the Hour 1 to be 00:00 the code is like

Duration.ToText(#duration(0,0, (Number.FromText(Text.End([Attribute], 1)) 1) * 30, 0))

So Hour 9 would be like 4:00.

I didn't get this part:

"Also, would there be a solution to just increment the time in the date column rather than creating a new separate column for this?"

Can you give me some example ?

[CalvinL]

Thank you @camargos88 

So I've got 48 values for half hour.. how would my code look like for that? Half hour 1 would be 00:30, half hour 2 would be 01:00 up to half hour 48 which would be 00:00

Re: "Also, would there be a solution to just increment the time in the date column rather than creating a new separate column for this?"

What i meant by that is - can we use a function like the DateAdd function to just increment the current date column by +00:30?

[camargos88]

Hi @CalvinL ,

I didn't try it after Hour 9, so the code would be like:

Duration.ToText(#duration(0, 0, 30, 0) * Number.FromText(Text.Split([Attribute], " "){2}))

Also, Hour 48 will be next day ?

I've updated the file.

[CalvinL]

@camargos88 Values are being recorded every half hour (30 mins) hence the 00:30. Sorry for any confusion.

[camargos88]

@CalvinL ,

Could you check the file ?

[CalvinL]

Hi @camargos88 

Thanks for that. That file seemed to work for me too.. appreciate you answering all of my questions! I've marked your original file post as the solution.