dataflow filter

[arkiboys2]

in dataflow gen2 how is it possible to do this filter on a dataset?

lower(trim(column2)) == lower(trim(column7))

this means I only want to ingest if column2 value is as same as column7

thank you

[frithjof_v · Accepted solution]

You need to manually edit the M code.

Advanced Editor is useful for this.

[arkiboys2]

many thanks

[frithjof_v]

Similar to this code (you can paste this code into a blank query):

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WysgvKk5VAAElHSUQ5eFfFOyqFKsTrZScWAKTADKLwWIu+elQdS7+7goQaZC4s2MIUBxEgnge/kFAM8DqwMxiJJVpOfnlqUVASTcf//DUIphMLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [column2 = _t, column7 = _t]),
  #"Trimmed text" = Table.TransformColumns(Source, {{"column2", each Text.Trim(_), type nullable text}}),
  #"Trimmed text 1" = Table.TransformColumns(#"Trimmed text", {{"column7", each Text.Trim(_), type nullable text}}),
  #"Lowercased text" = Table.TransformColumns(#"Trimmed text 1", {{"column7", each Text.Lower(_), type nullable text}}),
  #"Lowercased text 1" = Table.TransformColumns(#"Lowercased text", {{"column2", each Text.Lower(_), type nullable text}}),
  #"Filtered rows" = Table.SelectRows(#"Lowercased text 1", each [column2] = [column7])
in
  #"Filtered rows"