Help Creating Customized Filter

[zhoura345]

I have a table where I need to filter out some Trip Numbers. For each Trip there are 3 possible Activity Name values: Arrive, Sit, Stand. Each one comes with a date and time stamp in the 3rd column. What I need to do is filter out the Trip Numbers where the Date/Time stamps values are the same for Arrive and Sit. In my table here, it would mean filtering out Trip Numbers 1 and 3. Also, I need to retain the Stand rows as I'll be using them later.

Would appreciate help with going over the steps required (without coding directly into Advanced Editor if possible).

Here is the original data in table form:

Trip Number	Activity Name	Activity Date and Time
1	Arrive	6/4/2024 5:50:00 PM
1	Sit	6/4/2024 5:50:00 PM
1	Stand	6/4/2024 8:50:00 PM
2	Arrive	5/20/2024 8:21:00 AM
2	Sit	5/20/2024 9:55:00 AM
2	Stand	5/24/2024 12:10:00 PM
3	Arrive	5/2/2024 8:43:00 PM
3	Sit	5/2/2024 8:43:00 PM
3	Stand	5/4/2024 11:43:00 PM
4	Arrive	6/1/2024 3:30:00 AM
4	Sit	6/1/2024 1:30:00 PM
4	Stand	6/1/2024 9:30:00 PM

[dufoq3]

Hi @zhoura345, another solution:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdCxCsIwEMbxVymZC7273Ine1gcoCI6lg6BDF4dSfH4jSZpLQNwy/Mg/+ebZoevduG3r+xkOp4EHAuJOVEABuuvklj6i27r/E/v99bDmXBuyKQkkK8KvGouKrUIuKtKSFAso1ZAUbc43uVxj36Cj9lscsdzCGnG9Ikbl1YN5NpsVk8AkzDVlRcyfL2b5AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Trip Number" = _t, #"Activity Name" = _t, #"Activity Date and Time" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Trip Number", Int64.Type}, {"Activity Date and Time", type datetime}}, "en-US"),
    GroupedRows = Table.Group(ChangedType, {"Trip Number"}, {
        {"All", each _, type table},
        {"FilterHelper", each if Table.SelectRows(_, (x)=> x[Activity Name] = "Arrive")[Activity Date and Time]{0}? = Table.SelectRows(_, (x)=> x[Activity Name] = "Sit")[Activity Date and Time]{0}? then 0 else 1, Int64.Type}} ),
    FilteredRows = Table.SelectRows(GroupedRows, each ([FilterHelper] = 1)),
    CombinedAll = Table.Combine(FilteredRows[All])
in
    CombinedAll

[zhoura345]

Thank you so much! 🙂

[dufoq3]

You're welcome 😉

[ManuelBolz]

Hello @zhoura345,

if my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.

Try this solution:

let
    //Replace the first Step with your DataTable
    //Source = DataTable
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdCxCsIwEMbxVymZC7273Ine1gcoCI6lg6BDF4dSfH4jSZpLQNwy/Mg/+ebZoevduG3r+xkOp4EHAuJOVEABuuvklj6i27r/E/v99bDmXBuyKQkkK8KvGouKrUIuKtKSFAso1ZAUbc43uVxj36Cj9lscsdzCGnG9Ikbl1YN5NpsVk8AkzDVlRcyfL2b5AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Trip Number" = _t, #"Activity Name" = _t, #"Activity Date and Time" = _t]),
    Grouped = Table.Group(Source, {"Trip Number", "Activity Date and Time"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    Filtered = Table.SelectRows(Grouped, each [Count] > 1),
    Duplicates = Table.SelectColumns(Filtered, {"Trip Number", "Activity Date and Time"}),
    Merged = Table.NestedJoin(Source, {"Trip Number", "Activity Date and Time"}, Duplicates, {"Trip Number", "Activity Date and Time"}, "Duplicates", JoinKind.LeftOuter),
    Removed = Table.SelectRows(Merged, each Table.IsEmpty([Duplicates])),
    Cleaned = Table.RemoveColumns(Removed, {"Duplicates"})
in
    Cleaned

Best regards from Germany
Manuel Bolz

🟦Follow me on LinkedIn
🟨How to Get Your Question Answered Quickly
🟩Fabric Community Conference
🟪My Solutions on Github

[zhoura345]

Ty! Would it be possible to do without coding directly in Advanced Editor or if you could share your PBIX file?

Also, when I copied your code, I got this, which was not the same end result as the image I had above:

[ManuelBolz]

Hello @zhoura345,

you just have to replace the "source-step" in my code. The information is in the first 4 lines of my example.

If my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.

Best regards from Germany
Manuel Bolz

🟦Follow me on LinkedIn
🟨How to Get Your Question Answered Quickly
🟩Fabric Community Conference
🟪My Solutions on Github

[zhoura345]

Your result kept Trip Number 1 and 3, but I am looking to have those be gone, only keep 2 and 4 from this sample dataset.

[ManuelBolz · Accepted solution]

Hello @zhoura345,

If my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.

Okay, sorry, it's already late in Germany. Here is the right solution.

let
    //Replace the first Step with your DataTable
    //Source = DataTable
    
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdCxCsIwEMbxVymZC7273Ine1gcoCI6lg6BDF4dSfH4jSZpLQNwy/Mg/+ebZoevduG3r+xkOp4EHAuJOVEABuuvklj6i27r/E/v99bDmXBuyKQkkK8KvGouKrUIuKtKSFAso1ZAUbc43uVxj36Cj9lscsdzCGnG9Ikbl1YN5NpsVk8AkzDVlRcyfL2b5AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Trip Number" = _t, #"Activity Name" = _t, #"Activity Date and Time" = _t]),
    GroupedData = Table.Group(Source, {"Trip Number", "Activity Date and Time"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    Filtered = Table.SelectRows(GroupedData, each [Count] > 1),
    Duplicated = Table.SelectColumns(Filtered, {"Trip Number"}),
    Unique = Table.Distinct(Duplicated),
    Merged = Table.NestedJoin(Source, {"Trip Number"}, Unique, {"Trip Number"}, "Duplicates", JoinKind.LeftOuter),
    FilteredData = Table.SelectRows(Merged, each Table.IsEmpty([Duplicates])),
    CleanedData = Table.RemoveColumns(FilteredData, {"Duplicates"})
in
    CleanedData

Best regards from Germany
Manuel Bolz

🟦Follow me on LinkedIn
🟨How to Get Your Question Answered Quickly
🟩Fabric Community Conference

🟪My Solutions on Github

[zhoura345]

Perfect Thank You!! 🙏