Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
Thank you this worked pefectly! Makes sense to write it like that now I see it 🙂
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 ))
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)
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
It looks like REP is a value of type text. Is CSR also a text value when it is populated?
Yea both Tables/Lists are set as type Text and the columns are also type Text
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.