locked
Filter out rows of data based on the value of a column RRS feed

  • Question

  • I am trying to add a step in the Advanced editor to trim down the amount of rows that I am working with.

    In pseudocode, it would be:

    Table.SelectRows(#"LastStep", Only keep row if col A = Col D in that row)

    Is this something that I can even do?  The syntax of trying to reference two different columns in that matching condition is escaping me completely.

    Wednesday, September 6, 2017 5:17 AM

Answers

  • = Table.SelectRows(#"LastStep", each [HeaderNameOfColumnA] = [HeaderNameOfColumnD])
    Wednesday, September 6, 2017 1:05 PM

All replies

  • One approach is to add a new custom column such as If colA = colD then true else false Then filter on true for this new column.

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Wednesday, September 6, 2017 11:11 AM
  • = Table.SelectRows(#"LastStep", each [HeaderNameOfColumnA] = [HeaderNameOfColumnD])
    Wednesday, September 6, 2017 1:05 PM
  • Probably the way to go about it.  I got stuck on trying to make it work my way.
    Wednesday, September 6, 2017 6:03 PM
  • I tried this but kept having problems but perhaps they were with something else.  This should work as written Bill?
    Wednesday, September 6, 2017 6:04 PM
  • Put this code in the advanced editor window and see what happens.

    let
        Source = Table.FromRows({{"a",1,4,"c"}, {"b",2,5,"x"}, {"a",3,6,"a"}}, {"ColA","ColB","ColC","ColD"}),
        Filter = Table.SelectRows(Source, each [ColA] = [ColD])
    in
        Filter

    Thursday, September 7, 2017 1:42 PM