Forum Discussion
Table.SelectRows in conjunction with if statement from another table
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!
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
7 Replies
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 SelectRowsHello - I think the problem is in this part of the script:
each if CSR = nullThis 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)>0OR
each if Table.IsEmpty(CSR)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
CSRIt looks like REP is a value of type text. Is CSR also a text value when it is populated?