Table.SelectRows in conjunction with if statement from another table

[JamesRobson]

Hi,

 Trying to utilise an if statement within a Table.SelectRows and not having any joy, had a good look around and can't quite find the right thing....

So table Inventory(90+) lists all inventory line data and has 2 additional columns CSR and REP

I've created a "Home" tab with 2 tables (named CSR and REP) turn them into lists and add to the query so that the users can populate either one of these then automatically filter the data as required.

From there I want to filter the Inventory(90+) table based on whichever the user has populated. So let say I only wanted to filter by 1 criteria I use  = Table.SelectRows(#"Sorted Rows", each ([CSR] = CSR))  and that works fine but now I want to do that but if CSR = null then filter [REP] = REP.

I tried = Table.SelectRows(#"Sorted Rows", each (if CSR <> null then CSR else REP)) but I get error saying "We cannot convert value "BigJim" into type Logical" but I wasn't surprised by as that doesn't look right.

Then I tried 

= Table.SelectRows(#"Sorted Rows", each if CSR = null
then Table.SelectRows(#"Sorted Rows", each [REP] = REP)
else Table.SelectRows(#"Sorted Rows", each [CSR] = CSR))

but get a similar error "We cannot convert a value of type Table to type Logical"

Hope the above makes sense on what I'm trying to achieve and any help would be appreciated!

[jennratten · Accepted solution]

Assuming that your REP and CSR are values and not tables, and you want to use one conditional statement, you would do it like this...

= Table.SelectRows(#"Sorted Rows", each if CSR = null
then [REP] = REP else [CSR] = CSR )

Example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUpRitWJVkoCslLhrDQwKxnISgezUuCsVCArQyk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CSR = _t, REP = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CSR", type text}, {"REP", type text}}),
    CSR = null, //"b",
    REP = "h",
    SelectRows = Table.SelectRows(#"Changed Type" , each if CSR = null then [REP] = REP else [CSR] = CSR )
in
    SelectRows

[JamesRobson]

Thank you this worked pefectly! Makes sense to write it like that now I see it 🙂

[Anonymous]

non è assolutamente chiaro quello che cerchi.

sarebbe utile se caricassi delle tabelle di esempio.

Provo ad indovinare 🎰, comunque ...

se ti funziona  Table.SelectRows(#"Sorted Rows", each ([CSR] = CSR))  

allora prova  con Table.SelectRows(#"Sorted Rows", each ([CSR] = CSR or [REP] = REP ))  

[jennratten]

Hello - I think the problem is in this part of the script:

each if CSR = null

This is evaluating whether or not CSR is a null object, not a table without rows.  Pls try replacing that portion of the script with one of these options:

each if Table.RowCount(CSR)>0

OR

each if Table.IsEmpty(CSR)

[JamesRobson]

Hi Jennratten,

 Unfortunatley both of those give me "We cannot convert the value null to type Table."

= Table.SelectRows(#"Sorted Rows", each if Table.IsEmpty(CSR)
then Table.SelectRows(#"Sorted Rows", [REP] = REP)
else Table.SelectRows(#"Sorted Rows", [CSR] = CSR))

adding a screenshot of the CSR table drilldown incase I've gone wrong there but as before it works fine when using 1 criteria:

let
Source = Excel.CurrentWorkbook(){[Name="CSR"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CSR", type text}}),
#"Uppercased Text" = Table.TransformColumns(#"Changed Type",{{"CSR", Text.Upper, type text}}),
CSR = #"Uppercased Text"{0}[CSR]
in
CSR

[jennratten]

It looks like REP is a value of type text.  Is CSR also a text value when it is populated?

[JamesRobson]

Yea both Tables/Lists are set as type Text and the columns are also type Text