Forum Discussion

zhoura345's avatar
zhoura345
Frequent Visitor
2 years ago
Solved

Help Creating Customized Filter

 

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 NumberActivity NameActivity Date and Time
1Arrive6/4/2024 5:50:00 PM
1Sit6/4/2024 5:50:00 PM
1Stand6/4/2024 8:50:00 PM
2Arrive5/20/2024 8:21:00 AM
2Sit5/20/2024 9:55:00 AM
2Stand5/24/2024 12:10:00 PM
3Arrive5/2/2024 8:43:00 PM
3Sit5/2/2024 8:43:00 PM
3Stand5/4/2024 11:43:00 PM
4Arrive6/1/2024 3:30:00 AM
4Sit6/1/2024 1:30:00 PM
4Stand6/1/2024 9:30:00 PM

 

Need Help
  • ManuelBolz's avatar
    ManuelBolz
    2 years ago

    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

9 Replies

Replies have been turned off for this discussion
  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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
  • ManuelBolz's avatar
    ManuelBolz
    Responsive Resident
    2 years ago

    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