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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
zhoura345
Frequent Visitor

Help Creating Customized Filter

zhoura345_0-1718126391303.png

 

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.

 

zhoura345_1-1718126819692.png

 

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

 

1 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

View solution in original post

9 REPLIES 9
dufoq3
Super User
Super User

Hi @zhoura345, another solution:

 

Result

dufoq3_0-1718188089246.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Thank you so much! 🙂

You're welcome 😉


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

ManuelBolz
Super User
Super User

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

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_0-1718136746044.png

 

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

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.

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

Perfect Thank You!! 🙏

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors
Top Kudoed Authors