Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SteeltownRiot
Helper I
Helper I

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 IDTIMESTAMPStart TimeDurationPolicyViolation

Client

Index0Prev.INCIDENT_IDIndex1
A84634/2/21 8:30  Policy 1Violation 1Client B0 1
A84634/7/21 12:284/2/21 8:307,439Policy 1Violation 1Client B1A84632
..............................
C10404/14/21 11:114/13/21 10:16 Policy 2Violation 1Client A77J434178
C11454/14/21 11:114/14/21 11:11 Policy 3Violation 1Client A78C104079
F11664/14/21 11:114/14/21 11:11 Policy 4Violation 1Client A79C114580
D11674/14/21 11:114/14/21 11:11 Policy 5Violation 1Client A80F116681
D11674/14/21 15:034/14/21 11:11232Policy 5Violation 1Client A81D116782

 

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 12
SteeltownRiot
Helper I
Helper I

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.

v-kelly-msft
Community Support
Community Support

Hi  @SteeltownRiot ,

 

Use below M codes:

let
    Source = Table.SelectRows(Table,each [Duration]<>null and [Duration]<>"" and [Incident ID]<>[Prev.INCIDENT_ID])
in
    Source

Check my sample .pbix file attached.

 

Best Regards,
Kelly

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

Sorry, that produced a blank table

#"Custom Filter" = Table.SelectRows(#"Renamed Columns", each [Duration] <> null and [Incident ID] <> [Prev.INCIDENT_ID])

Hi @SteeltownRiot ,

 

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,
Kelly

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

edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.

Hi @SteeltownRiot ,

 

Is your issue solved now?Have you checked my last reply?

 

Best Regards,
Kelly

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

@v-kelly-msft, it is not yet resolved. I did see your last. I've just circled back to this project this afternoon. I am going to spend some time dummying up the raw data more fully and trying to clean up the OG post to be more clear and useful, as suggested by @edhans.

Hi  @SteeltownRiot ,

 

But test here,it works fine,could you advise me where is the problem?

 

Best Regards,
Kelly

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

Test where @v-kelly-msft ?
I still need to figure out how to make this code dance for me the way I want. I am hoping providing more detail will help someone see what I am missing but fear it may not be possible.

Hi @SteeltownRiot ,

 

Have you checked my sample .pbix file?

 

Best Regards,
Kelly

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

@v-kelly-msft I have checked your .pbix and tested it in my code.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors