Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
I think this will help: https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389
@CalvinL paste sample data, what column identifies the time?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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^
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
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 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 |
@CalvinL ,
The m code does it.
Check this file: Download PBIX
Did I answer your question? Mark my post as a solution!
Ricardo
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?
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 ?
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?
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.
@camargos88 Values are being recorded every half hour (30 mins) hence the 00:30. Sorry for any confusion.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |