Forum Discussion
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...
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
dufoq3
Community Champion
Community ChampionHi 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