The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello people of the internet! _o/
First time posting for help anywhere in general so bare with me...
I have two columns of DateTime data (in and out).
I want to calculate the count of events in que when a new event is registred based on these DateTime columns.
Here is the example data with expected result (CountInQue column):
ID | TimeIn | TimeOUT | CountInQue |
1 | 30.7.2022 5:21 | 30.7.2022 5:43 | 0 |
2 | 1.8.2022 6:01 | 1.8.2022 6:14 | 0 |
3 | 1.8.2022 6:28 | 1.8.2022 6:44 | 0 |
4 | 1.8.2022 6:31 | 1.8.2022 6:53 | 1 |
5 | 1.8.2022 6:59 | 1.8.2022 7:18 | 0 |
6 | 1.8.2022 7:16 | 1.8.2022 7:35 | 1 |
7 | 1.8.2022 7:20 | 1.8.2022 7:54 | 1 |
8 | 1.8.2022 7:28 | 1.8.2022 8:04 | 2 |
9 | 1.8.2022 7:45 | 1.8.2022 8:27 | 2 |
10 | 1.8.2022 8:31 | 1.8.2022 9:05 | 0 |
11 | 1.8.2022 8:40 | 1.8.2022 9:12 | 1 |
12 | 1.8.2022 8:57 | 1.8.2022 9:23 | 2 |
I bolded two examples to get the idea what I'm looking for.
I tried to use this post: https://community.powerbi.com/t5/Power-Query/Count-rows-if-between-two-time-slots/td-p/1424481 but the outcome counts the values the wrong way around. This example count the rows that happen between certain rows time periods. I have also been trying to modify the measure in the previous link without any success.
Any suggestions? 😥
Solved! Go to Solution.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY8xDsIwDEWvgjJXle3YTZoVlk4IqUxVxw4MlIXevyFFBmfz93v6sqfJDRfXuPHxXIb1O1zvY57Or219D+ttW9zcTA7zykMbWgKikySqF+zzAopLecI2HqRLgDYjq+ktoWgz/0y2xFedUpqKKRXp/3NIGLWzq0iVvWhnsITAZmE1Y2WaHBN8TCpmdReLNSmoiWCRfb5PIPoSolUZrIqklyJZVYJVyR8HzDs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Promoted Headers", {{"TimeIn", type datetime},{"TimeOUT", type datetime}}, "de-DE"),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"ID", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "InQueue", (k)=> Table.RowCount(Table.SelectRows(#"Changed Type",each [ID]<k[ID] and [TimeOUT]>k[TimeIn]))),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"InQueue", Int64.Type}})
in
#"Changed Type1"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
There are lots of gems in them there M functions. Let me know if you like to learn more.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY8xDsIwDEWvgjJXle3YTZoVlk4IqUxVxw4MlIXevyFFBmfz93v6sqfJDRfXuPHxXIb1O1zvY57Or219D+ttW9zcTA7zykMbWgKikySqF+zzAopLecI2HqRLgDYjq+ktoWgz/0y2xFedUpqKKRXp/3NIGLWzq0iVvWhnsITAZmE1Y2WaHBN8TCpmdReLNSmoiWCRfb5PIPoSolUZrIqklyJZVYJVyR8HzDs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Promoted Headers", {{"TimeIn", type datetime},{"TimeOUT", type datetime}}, "de-DE"),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"ID", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "InQueue", (k)=> Table.RowCount(Table.SelectRows(#"Changed Type",each [ID]<k[ID] and [TimeOUT]>k[TimeIn]))),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"InQueue", Int64.Type}})
in
#"Changed Type1"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
Thank you sir!
This solved the issue after some tuning.
I have never used M functions and this was way better sollution than what I had in mind.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
139 | |
112 | |
108 | |
74 | |
58 |
User | Count |
---|---|
263 | |
131 | |
123 | |
100 | |
96 |