Frequent Visitor

2 years ago

Solved

Deleting Rows based on value of previous row

Hello Experts,

I want to delete a row if the status field repeats on the next row. In all cases its the status value of Start that can repeat itself when there is no corresponding status value of End.

The table values are from a trace file that is saved to Application Insights logs and the output is in order the trace records are written to the logs. After the incomplete record pair has been removed I will create an index to pairt the Start and End status to caluclate the time taken to snyc records from application. Creating the index won't be a problem once I remove the Sync start record from the table.

Thanks in Advance.

Need Help

danextian

2 years ago

Hi pksymonds

Given the additional information, here's the logic that I think should work:

Grouped the table by session_id
Count the number of rows each group has. If it is odd then it can indicate that one of the rows doesn't have a pair
Add an index column to the grouped rows. The index will reset at every session_id.
Expand the grouped table with an index column
Compare the index against the row count. If row count is odd and index = row count then that row is to be removed.
Clean up.

Here's the updated code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tdI9EoIwEIbhqzjU7JDdbH6W3hNYOhQJSegsHA7kWTyZ+NMpDIzaUT3z5luOxyoqy5FzAFFZA0uK4GMqEIplJ1J0trGqq+vlMIbzOH2Rb5AaUqR3iC1z1dUblP0pvRtmmzFTslH5Y4l8X2LwYSRkj0UjKCk0GZLBh95BNug9JeIsOFtiW/O8jlEuoJcITscAHOwUIb2CkGzKaIlj0rOKa0lvU97fMxmrlx2GhWlfs6xnPl559X0WW9b/LEstv9nlznQ3", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [session_Id = _t, Status = _t, #"Date/Time" = _t]),
    #"Sorted Rows" = Table.Sort(Source,{{"session_Id", Order.Ascending}, {"Date/Time", Order.Ascending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"session_Id", type text}, {"Status", type text}, {"Date/Time", type datetime}}),
    #"Grouped Rows" = Table.Group(Table.Buffer(#"Changed Type"), {"session_Id"}, {{"Grouped", each _, type table [session_Id=nullable text, Status=nullable text, #"Date/Time"=nullable datetime]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Row Count", each Table.RowCount([Grouped]), Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Added Index to Grouped", each Table.AddIndexColumn([Grouped],"Index", 1), type table),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Grouped"}),
    #"Expanded Added Index to Grouped" = Table.ExpandTableColumn(#"Removed Columns", "Added Index to Grouped", {"Status", "Date/Time", "Index"}, {"Status", "Date/Time", "Index"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Added Index to Grouped", "Filter", each [Index] = [Row Count] and Number.IsOdd([Row Count]), type logical),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Status", type text}, {"Date/Time", type datetime}, {"Index", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [Filter] <> true)
in
    #"Filtered Rows"

7 Replies

danextian
Super User
2 years ago
Hi pksymonds

If you are attempting to delete specific rows from a data source, Power BI is not meant for that - M the language behind Power Query is a functional programming language, not object-based so it cannot delete or modify records. If this isn't the case, please provide sample data and your expected result from that sample data. Please read this sticky post - https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/1447523/jump-to/first-unread-message

pksymonds

Frequent Visitor

2 years ago

Hello danextian

Thanks for responding.

Below is the table in its original format.

session_Id	Status	Date/Time
b064b4ea-90e3-49db-8bdf-af64799f3e6b	Start	28/12/2023 11:44
b064b4ea-90e3-49db-8bdf-af64799f3e6b	End	28/12/2023 11:45
b064b4ea-90e3-49db-8bdf-af64799f3e6b	Start	28/12/2023 11:45
b064b4ea-90e3-49db-8bdf-af64799f3e6b	End	28/12/2023 11:45
b064b4ea-90e3-49db-8bdf-af64799f3e6b	Start	28/12/2023 11:49
b064b4ea-90e3-49db-8bdf-af64799f3e6b	End	28/12/2023 11:51
d1481f31-09f2-499e-8ac7-e51882d24e91	Start	28/12/2023 16:54
507a189b-73ba-4a6f-a9c0-ad6de1624bd3	Start	28/12/2023 17:23
507a189b-73ba-4a6f-a9c0-ad6de1624bd3	End	28/12/2023 17:25

And this is what I am trying to achieve.

session_Id	Status	Date/Time
b064b4ea-90e3-49db-8bdf-af64799f3e6b	Start	28/12/2023 11:44
b064b4ea-90e3-49db-8bdf-af64799f3e6b	End	28/12/2023 11:45
b064b4ea-90e3-49db-8bdf-af64799f3e6b	Start	28/12/2023 11:45
b064b4ea-90e3-49db-8bdf-af64799f3e6b	End	28/12/2023 11:45
b064b4ea-90e3-49db-8bdf-af64799f3e6b	Start	28/12/2023 11:49
b064b4ea-90e3-49db-8bdf-af64799f3e6b	End	28/12/2023 11:51
507a189b-73ba-4a6f-a9c0-ad6de1624bd3	Start	28/12/2023 17:23
507a189b-73ba-4a6f-a9c0-ad6de1624bd3	End	28/12/2023 17:25

To achieve what I want I think I will need to copy the orginal into a sql table and create a view using the LAG function.

Regards,

Paul.

Ashish_Mathur

Super User

2 years ago

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"session_Id", type text}, {"Status", type text}, {"Date/Time", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Status"}, {{"Count", each Table.Max(_,"Date/Time")}},GroupKind.Local),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"session_Id", "Date/Time"}, {"session_Id", "Date/Time"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Count",{{"Date/Time", type datetime}})
in
    #"Changed Type1"

Hope this helps.

danextian

Super User

2 years ago

Hi pksymonds ,

Assuming that the count of rows per session id must always be even to be able say that it is a complete pair/s, try this M Code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tdE7DsIwDIDhq6DOtRo7zsPdOQFj1cFpkpEB9UCchZMRwVh1iBCbp0+/7WUZkvGcuCiIKRZYcoKYcgWtnoNItcWnYRxez9uuj71NFCekiQzZC+LMPKxjh3K956Ph+oyTkk7ljyXye4nDj5GRI1aLYKRSM6RA1C1AcRgjZeIieFriZ/f9jjNBMUqCYJMCq28RshnQ7HNBT5yyPVXCTLZPOe7TjHbZ9Q0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [session_Id = _t, Status = _t, #"Date/Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"session_Id", type text}, {"Status", type text}, {"Date/Time", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Check", each let
//t refers to the name of the previously applied step
t = #"Changed Type",
s = [session_Id],
select = Table.SelectRows(t, each [session_Id] = s),
//count the number of rows per session id
count = Table.RowCount(select),
//check if count is even
IsEven = Number.IsEven(count)
in IsEven
,type logical),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Check] = true),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Check"})
in
    #"Removed Columns"

RE Deleting Rows based on value of previous row.pbix22 KB

pksymonds

Frequent Visitor

2 years ago

Hello danextian

Thanks for the suggestion.

I noticed that the entire session is filtered out if the record number is odd. I was trying to remove the last record that doesn't have a corresponding start or end status (message.2.1) but this is proving to be difficult.

For now I have kept just the rows that have value of TRUE as you did in your example.

Regards,

Paul.

danextian

Super User

2 years ago

Hi pksymonds

Given the additional information, here's the logic that I think should work:

Grouped the table by session_id
Count the number of rows each group has. If it is odd then it can indicate that one of the rows doesn't have a pair
Add an index column to the grouped rows. The index will reset at every session_id.
Expand the grouped table with an index column
Compare the index against the row count. If row count is odd and index = row count then that row is to be removed.
Clean up.

Here's the updated code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tdI9EoIwEIbhqzjU7JDdbH6W3hNYOhQJSegsHA7kWTyZ+NMpDIzaUT3z5luOxyoqy5FzAFFZA0uK4GMqEIplJ1J0trGqq+vlMIbzOH2Rb5AaUqR3iC1z1dUblP0pvRtmmzFTslH5Y4l8X2LwYSRkj0UjKCk0GZLBh95BNug9JeIsOFtiW/O8jlEuoJcITscAHOwUIb2CkGzKaIlj0rOKa0lvU97fMxmrlx2GhWlfs6xnPl559X0WW9b/LEstv9nlznQ3", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [session_Id = _t, Status = _t, #"Date/Time" = _t]),
    #"Sorted Rows" = Table.Sort(Source,{{"session_Id", Order.Ascending}, {"Date/Time", Order.Ascending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"session_Id", type text}, {"Status", type text}, {"Date/Time", type datetime}}),
    #"Grouped Rows" = Table.Group(Table.Buffer(#"Changed Type"), {"session_Id"}, {{"Grouped", each _, type table [session_Id=nullable text, Status=nullable text, #"Date/Time"=nullable datetime]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Row Count", each Table.RowCount([Grouped]), Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Added Index to Grouped", each Table.AddIndexColumn([Grouped],"Index", 1), type table),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Grouped"}),
    #"Expanded Added Index to Grouped" = Table.ExpandTableColumn(#"Removed Columns", "Added Index to Grouped", {"Status", "Date/Time", "Index"}, {"Status", "Date/Time", "Index"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Added Index to Grouped", "Filter", each [Index] = [Row Count] and Number.IsOdd([Row Count]), type logical),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Status", type text}, {"Date/Time", type datetime}, {"Index", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [Filter] <> true)
in
    #"Filtered Rows"

pksymonds
Frequent Visitor
2 years ago
Hello danextian

Thanks for the updated logic. I am able to get the desired outcome for the table.

Much appreciated.

Kind Regards.

Forum Discussion

Deleting Rows based on value of previous row

7 Replies

Related content

delete rows based on conditions

Can't delete Fabric items no Delete option available

Can't Delete Item

Getting value for previous year based on value name and year slicer

Dynamic Table Name Based on Previous Month