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...
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

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

zhoura345's avatar
zhoura345
Frequent Visitor
2 years ago

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:

 

    • zhoura345's avatar
      zhoura345
      Frequent Visitor
      2 years ago

      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'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.

        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