Forum Discussion
Custom Filtering
I have a data set for incidents that gets a little messy. There should be two or three records for each incident ID (created, acknowledged, closed) but not all incidents are acknowledged (well most incidents are not acknowledged, but that's a whole different discussion). To make it juicier, sometimes the very thing causing the incident disrupts the ingestion of the incident data itself so sometimes I only get the open, ack, or close by itself. (An example of the raw data pulled into Power Query after removing unneccessary columns can be found in a comment below.)
My first problem is to calculate the duration in minutes between the first and last incident with the same ID. I have something that seems to be working there, but is probably inelegant:
The bigger problem is I need to display these as a single line, which means removing all of the rows that do not have calculated durations except those that only have one record. For instance:
| Incident ID | TIMESTAMP | Start Time | Duration | Policy | Violation | Client | Index0 | Prev.INCIDENT_ID | Index1 |
| A8463 | 4/2/21 8:30 | Policy 1 | Violation 1 | Client B | 0 | 1 | |||
| A8463 | 4/7/21 12:28 | 4/2/21 8:30 | 7,439 | Policy 1 | Violation 1 | Client B | 1 | A8463 | 2 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| C1040 | 4/14/21 11:11 | 4/13/21 10:16 | Policy 2 | Violation 1 | Client A | 77 | J4341 | 78 | |
| C1145 | 4/14/21 11:11 | 4/14/21 11:11 | Policy 3 | Violation 1 | Client A | 78 | C1040 | 79 | |
| F1166 | 4/14/21 11:11 | 4/14/21 11:11 | Policy 4 | Violation 1 | Client A | 79 | C1145 | 80 | |
| D1167 | 4/14/21 11:11 | 4/14/21 11:11 | Policy 5 | Violation 1 | Client A | 80 | F1166 | 81 | |
| D1167 | 4/14/21 15:03 | 4/14/21 11:11 | 232 | Policy 5 | Violation 1 | Client A | 81 | D1167 | 82 |
So I need to:
DISPLAY all rows WHERE (
"Prev.INCIDENT_ID" IS NOT NULL
)
DISPLAY all rows WHERE (
"Duration" IS NOT NULL
OR (
"Duration" IS NULL
AND
"Incident ID" NOT EQUAL to "Prev.INCIDENT_ID"
)
)
So, I tried:
let
Source = ||SOURCE||,
#"Removed Columns" = Table.RemoveColumns(Source,{"ALERTS_ID", "ACCOUNT_ID", "ACCOUNT_NAME", "CONDITION_ID", "CONDITION_NAME", "INCIDENT_API_URL", "INCIDENT_URL", "POLICY_URL", "CHART_URL", "EVENT_TYPE", "ID", "NAME", "LINK", "TYPE", "SERVICE", "PRODUCT", "TEAM", "UPLOAD_DT_TM", "UPDT_DT_TM", "PARAMETER"}),
#"Sorted Rows1" = Table.Sort(#"Removed Columns",{{"INCIDENT_ID", Order.Ascending}, {"TIMESTAMP", Order.Ascending}}),
#"Added Index1" = Table.AddIndexColumn(#"Sorted Rows1", "Index1", 0, 1, Int64.Type),
#"Added Index2" = Table.AddIndexColumn(#"Added Index1", "Index2", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index2", {"Index1"}, #"Added Index2", {"Index2"}, "Prev", JoinKind.LeftOuter),
#"Expanded Prev" = Table.ExpandTableColumn(#"Merged Queries", "Prev", {"INCIDENT_ID", "CURRENT_STATE", "TIMESTAMP"}, {"Prev.INCIDENT_ID", "Prev.CURRENT_STATE", "Start Time"}),
#"Added Custom" = Table.AddColumn(#"Expanded Prev", "Difference", each if [INCIDENT_ID] = [Prev.INCIDENT_ID] and [#"Prev.CURRENT_STATE"] = "CRITICAL" and [#"CURRENT_STATE"] = "OK" then [TIMESTAMP] - [Start Time] else null, type nullable duration),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Duration", each [Difference] * 1440),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"INCIDENT_ID", "TIMESTAMP", "Start Time", "Duration", "POLICY_NAME", "DETAILS", "SEVERITY", "CLIENT", "CURRENT_STATE", "OWNER", "TENANT_ID", "Index1", "Prev.INCIDENT_ID", "Prev.CURRENT_STATE", "Index2", "Difference"}),
#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"TIMESTAMP"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"INCIDENT_ID", "Incident ID"}, {"POLICY_NAME", "Policy"}, {"DETAILS", "Violation"}}),
#"Custom Filter1" = Table.SelectRows(#"Renamed Columns", each [Prev.INCIDENT_ID] <> null),
#"Custom Filter2" = Table.SelectRows(#"Custom Filter1", each ([Duration] <> null or ([Duration] = null and [Incident ID] <> [Prev.INCIDENT_ID] and [Prev.CURRENT_STATE] = "CRITICAL")))
in
#"Custom Filter2"
This has given me the closest result to what I need, except it occassionally returns the Open incident and the Closed incident records following a single Open record entry.
12 Replies
- edhansCommunity Champion
Can you tighten this up a bit? You haven't given us the raw data. You have given us an intermediate step that isn't working and explained that is is broken.
As far as your SelectRows function, it works fine here - not sure it is the results you want, but it functionally works and you can see it in the last statement.Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldG9CoMwFAXgV5HMgrk/mphNLAVLh1JKF+lQOglSly59+ybRgLEOOiTkQDjfjbatqDQXJFLBGWYIiTYkbUqmdRn67vVNwB7v3dA/P93w9qm+Nremrs72GO6DeKTzPuX6AA3qv3qVMpUb610MpeiJGiRL3wnsDTAAYyafpYEifgBGAsaCUnY7MbErUXoygPN1I8ozgyKDFob7CGFwVXrjCFAU+wyODF4YpTfGwbX0xsEaap+RR0YeG9rNHwbXsG7kRtKKgYRbFXc91Gr71x8/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Incident ID" = _t, TIMESTAMP = _t, #"Start Time" = _t, Duration = _t, Policy = _t, Violation = _t, SEVERITY = _t, Index0 = _t, Prev.INCIDENT_ID = _t, Index1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"TIMESTAMP", type datetime}, {"Start Time", type datetime}, {"Duration", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Duration]<> null and [Duration] <> "" or ([Incident ID] = [Prev.INCIDENT_ID]))) in #"Filtered Rows"How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.- AnonymousNot applicable
Sorry, I've been away for a few days. I did not want to recreate the entirety of my work and dataset as example data because the previous steps have worked, but I will give it a go. It may take some time, though.
- v-kelly-msftCommunity Support
Hi Anonymous ,
Is your issue solved now?Have you checked my last reply?
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
- v-kelly-msftCommunity Support
Hi Anonymous ,
Use below M codes:
let Source = Table.SelectRows(Table,each [Duration]<>null and [Duration]<>"" and [Incident ID]<>[Prev.INCIDENT_ID]) in SourceCheck my sample .pbix file attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
- AnonymousNot applicable
Sorry, that produced a blank table
#"Custom Filter" = Table.SelectRows(#"Renamed Columns", each [Duration] <> null and [Incident ID] <> [Prev.INCIDENT_ID])- v-kelly-msftCommunity Support
Hi Anonymous ,
Yes,based on your data,there is no row that meet your all criteria:
for
[Duration]<>null and [Duration]<>""There are only 2 rows left,but in the 2 rows,[Incident ID]=[Prev.INCIDENT_ID]
That's why you get a blank table.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
- AnonymousNot applicable
Not sure what is going on with the tables right now. It is rejecting the HTML dictating the column widths it generates itself.
It also keeps deleting the post with my example data in it.