Forum Discussion
Solved
How can I group like rows ?
I have a table with the following: NAME KEY Added DATA-123 Removed DATA-123 Committed DATA-456 Added DATA-789 What I need is to know if the KEY has a Removed, to filter t...
I decided on making a clone of the table and just having "removed" in there and merge it back and filter out those records with Key as null, it appears to work and needed to have this due to time sensitivity, but thanks all for your assitance and ideas.
Assuming the source:
Power Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxJSU1R0lFycQxx1DU0MlaK1YlWCkrNzS/DFHbOz83NLClBSJiYmoElUAwxt7BUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Key = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Key", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Name", Text.Trim, type text}, {"Key", Text.Trim, type text}}),
#"Filtered Rows" = Table.SelectRows(#"Trimmed Text", each ([Name] = "Removed")),
#"Merged Queries" = Table.NestedJoin(#"Trimmed Text", {"Key"}, #"Filtered Rows", {"Key"}, "Filtered Rows", JoinKind.LeftAnti),
#"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Filtered Rows"})
in
#"Removed Columns"
or
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxJSU1R0lFycQxx1DU0MlaK1YlWCkrNzS/DFHbOz83NLClBSJiYmoElUAwxt7BUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Key = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Key", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Name", Text.Trim, type text}, {"Key", Text.Trim, type text}}),
#"Added Custom" = Table.AddColumn(#"Trimmed Text", "To Be Removed", each Table.MatchesAnyRows(Source, (R) => R[Key] = [Key] and Text.Lower(R[Name]) = "removed")),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([To Be Removed] = false)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"To Be Removed"})
in
#"Removed Columns"
Output: