Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
This is a two part question.
Firstly - what is the forumla or the calculation that can produce number of minutes,hours spent on an email with the same subject?
Secondly - how can I add a note/remarks on a visual bar that can be shown when the specific month is selected (ex: Jan) and hidden for the next month data (ex:Feb)?
Subject | FolderPath | Date Received | Time Received | Date Sent | Time Sent |
test layers | inbox\lyc | 01/24/2024 | 10:11:56 AM | 01/24/2024 | 10:11:55 AM |
test layers | inobx\sent | 01/24/2024 | 10:07:00 AM | 01/24/2024 | 10:07:03 AM |
test layers | inbox\coa | 01/26/2024 | 11:53:14 AM | 01/26/2024 | 11:53:17 AM |
source code | inbox\coa | 02/19/2024 | 12:53:14 PM | 02/20/2024 | 12:53:17 PM |
assignment functional | inbox\ | 02/01/2024 | 4:53:14 PM | 02/19/2024 | 4:53:17 PM |
source code | inbox\sent | 02/20/2024 | 2:53:14 PM | 02/20/2024 | 2:53:17 PM |
Hi @geeklarokcmie ,
This is accomplished by using the MEASURE approach to your request
Here is my test data
Create two measures about hour and minute
Duration_hour =
VAR First_datetime =
CALCULATE(
MIN('Table'[Received]),
ALLEXCEPT(
'Table',
'Table'[Subject]
)
)
VAR Last_datetime =
CALCULATE(
MAX('Table'[Sent]),
ALLEXCEPT(
'Table',
'Table'[Subject])
)
RETURN
DATEDIFF(First_datetime,Last_datetime,HOUR)
Duration_minute =
VAR First_datetime =
CALCULATE(
MIN('Table'[Received]),
ALLEXCEPT(
'Table',
'Table'[Subject]
)
)
VAR Last_datetime =
CALCULATE(
MAX('Table'[Sent]),
ALLEXCEPT(
'Table',
'Table'[Subject])
)
RETURN
DATEDIFF(First_datetime,Last_datetime,MINUTE)
Final output
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Appreciate providing two measures for my query @v-heq-msft.
However, I'm looking to get the amount of hours/minutes spent when the email has same subject title.
How is that achievable?
Hi @geeklarokcmie ,
Here's my modified version of the pbix file above, where you can create a column to calculate the duration of each row of data, and then go back and categorize it by SUBJECT to calculate the total duration of each SUBJECT.
Spend_hour = DATEDIFF('Table'[Received],'Table'[Sent],HOUR)
Spend by subject_hour =
CALCULATE(
SUM('Table'[Spend_hour]),
ALLEXCEPT(
'Table',
'Table'[Subject]
)
)
Spend_minute = DATEDIFF('Table'[Received],'Table'[Sent],MINUTE)
Spend by subject_minute =
CALCULATE(
SUM('Table'[Spend_minute]),
ALLEXCEPT(
'Table',
'Table'[Subject]
)
)
Final output
If this is not the result you want, please clearly show the result you want in excel or screenshot form so we can help you faster
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Here is what I'm looking for :
Folder Path | Subject | DateTimeSent | DateTimeReceived | Output |
\Inbox\PA\PI | ProductionReview | 2/26/2024 8:00 | 2/26/2024 8:00 | Number of days/hours/minutes it took for this email with same subject line |
\Inbox\PA\PI | ProductionReview | 2/23/2024 8:00 | 2/23/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 2/22/2024 8:00 | 2/22/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 2/21/2024 8:00 | 2/21/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 2/20/2024 8:00 | 2/20/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 2/16/2024 8:00 | 2/16/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 2/15/2024 8:00 | 2/15/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 2/14/2024 8:00 | 2/14/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 2/13/2024 8:00 | 2/13/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 2/12/2024 8:00 | 2/12/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 2/9/2024 8:00 | 2/9/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 2/8/2024 8:00 | 2/8/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 2/7/2024 8:00 | 2/7/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 2/6/2024 8:00 | 2/6/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 2/5/2024 8:00 | 2/5/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 2/2/2024 8:00 | 2/2/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 2/1/2024 8:00 | 2/1/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 1/31/2024 8:00 | 1/31/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 1/30/2024 8:00 | 1/30/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 1/29/2024 8:00 | 1/29/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 1/26/2024 8:00 | 1/26/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 1/25/2024 8:00 | 1/25/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 1/24/2024 8:00 | 1/24/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 1/23/2024 8:00 | 1/23/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 1/22/2024 8:00 | 1/22/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 1/19/2024 8:00 | 1/19/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 1/18/2024 8:00 | 1/18/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 1/17/2024 8:00 | 1/17/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 1/16/2024 8:00 | 1/16/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 1/12/2024 8:00 | 1/12/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 1/11/2024 8:00 | 1/11/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 1/10/2024 8:00 | 1/10/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 1/9/2024 8:00 | 1/9/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 1/8/2024 8:00 | 1/8/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 1/5/2024 8:00 | 1/5/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 1/4/2024 8:00 | 1/4/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 1/3/2024 8:00 | 1/3/2024 8:00 | |
\Inbox\PA\PI | ProductionReview | 1/2/2024 8:00 | 1/2/2024 8:00 | |
\Inbox\AC\ | RE: Setup Request | 1/24/2024 10:16 | 1/24/2024 10:16 | Number of days/hours/minutes it took for this email |
\Inbox\AC\ | RE: Setup Request | 1/23/2024 11:45 | 1/23/2024 11:45 | |
\Inbox\AC\ | Setup Request | 1/22/2024 12:06 | 1/22/2024 12:06 | |
\Sent Items\ | Setup Request | 1/25/2024 14:08 | 1/25/2024 14:08 | |
\Sent Items\ | FW: Setup Request | 1/23/2024 15:39 | 1/23/2024 15:39 | |
\Sent Items\ | FW: Setup Request | 1/23/2024 10:13 | 1/23/2024 10:12 | |
\Sent Items\ | RE: Setup Request | 1/22/2024 16:45 | 1/22/2024 16:45 |
Hi @geeklarokcmie ,
Thanks to @wini_R for troubleshooting the first problem.
Here's how I troubleshoot the second problem
1. Create a measure
Measure = IF(SELECTEDVALUE(Query1[Sent].[MonthNo])= 1 ,"Janurary","")
2.Create a slicer
3.The first method is to use subheadings for comments
4.The second way is to use the detailed data lable for annotations
Final output
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @geeklarokcmie,
As of your first question - you can calcuate this in power query using the following steps (just copy & paste it into advanced editor):
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBLDoMgGAavQlw38QdRU3Y9gEn36gIpbUwsJIJJvX2hPutjSSbfMJDngZXGoob3sjXBJahVpT9F0fTCHQCHhIYECHUHDAxjFifolp2g2KPyslfqyimNVPZgCCkDOHZ6FJ05f5lC83GXzDsXEjFMF+UWpZPS6K4VEgn9kDslCfF13pFRec8GRGCDUo+8khtTv9TbPRU9OyVsrRVvFvkw91HDnG7Ey53033uYOv3oOug8dV1afgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Subject = _t, FolderPath = _t, #"Date Received" = _t, #" Time Received" = _t, #"Date Sent" = _t, #"Time Sent" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Received", type date}, {"Date Sent", type date}, {" Time Received", type time}, {"Time Sent", type time}}),
#"Merged Date and Time" = Table.CombineColumns(#"Changed Type", {"Date Received", " Time Received"}, (columns) => List.First(columns) & List.Last(columns), "Received"),
#"Merged Date and Time1" = Table.CombineColumns(#"Merged Date and Time", {"Date Sent", "Time Sent"}, (columns) => List.First(columns) & List.Last(columns), "Sent"),
#"Grouped Rows" = Table.Group(#"Merged Date and Time1", {"Subject"}, {{"aa", each _, type table }}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "duration", each List.Max([aa][Sent]) - List.Min([aa][Received])),
#"Expanded aa" = Table.ExpandTableColumn(#"Added Custom", "aa", {"FolderPath", "Received", "Sent"}, {"FolderPath", "Received", "Sent"}),
#"Calculated Total Hours" = Table.TransformColumns(#"Expanded aa",{{"duration", Duration.TotalHours, type number}})
in
#"Calculated Total Hours"
Outcome:
Hello @wini_R - Thank you for providing the calculation.
However, my data actually looks like below where date and time are in same column and their data type is date/time. How can I tweak your calculation to get the desired results?
Subject | FolderPath | DateTime Received | Date Time Sent |
test layers | inbox\lyc | 1/24/2024 10:11:56 AM | 1/24/2024 10:11:55 AM |
test layers | inobx\sent | 1/24/2024 10:07:00 AM | 1/24/2024 10:07:03 AM |
test layers | inbox\coa | 1/26/2024 11:53:14 AM | 1/26/2024 11:53:17 AM |
source code | inbox\coa | 2/19/2024 12:53:14 PM | 2/20/2024 12:53:17 PM |
assignment functional | inbox\ | 2/1/2024 4:53:14 PM | 2/19/2024 4:53:17 PM |
source code | inbox\sent | 2/20/2024 2:53:14 PM | 2/20/2024 2:53:17 PM |
Hi @geeklarokcmie,
Please see if the following query helps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZDLDsIgEEV/hbA2KSDa2J2JcWdi1F11QemomApJoUb/XmjxUVOXc8I9c4c8x9umuIB0eISXpiqhXgt39sNCONipK6ANSFA3KCNDLdyCdvgwyrED61AlHlBb/0Dpwtz3++oh/UATxhNGGEeUZJRmkymar4b4JPAhmSm8zIZVPymSZoQM2AIf/7O11aQRXWgaQ37/OKP8Levz9CWzpqklIGlK+JWxhM5iiEXZetVyRno8DTzIhLXqpK/+MHRstHTKaFF9tJ2zi/K+8bWJ94WD7eLHfWr8afdd7vAE", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Promoted Headers", {{"DateTime Received", type datetime}, {"Date Time Sent", type datetime}}, "en-US"),
#"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Subject"}, {{"tab2", each _, type table [Subject=nullable text, FolderPath=nullable text, DateTime Received=nullable datetime, Date Time Sent=nullable datetime]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "duration", each List.Max([tab2][Date Time Sent]) - List.Min([tab2][DateTime Received])),
#"Expanded aa" = Table.ExpandTableColumn(#"Added Custom", "tab2", {"FolderPath", "DateTime Received", "Date Time Sent"}, {"FolderPath", "Received", "Sent"}),
#"Calculated Total Hours" = Table.TransformColumns(#"Expanded aa",{{"duration", Duration.TotalHours, type number}})
in
#"Calculated Total Hours"
This doesn't seem to work for me at all. I run into syntax errors.
Can you tell me all the applied steps so I can create that in PowerQuery.
It seems you just added one query into the other and ended up with 2 separate ones which cannot work. If you want to combine them, try to add the query above starting from 'Grouped rows' step and make sure it refers to the previous step in your current query.
Thank you for some reason. It still doesn't work for me.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
144 | |
72 | |
62 | |
52 | |
48 |
User | Count |
---|---|
208 | |
89 | |
61 | |
59 | |
57 |