Problem with FindRow() for picklists

I have a Table from which I want to retrieve an item based upon two columns, both of which are picklists:


Those two picklists are referenced to other Tables.

I have two questions:

  1. How should I write a FindRow statement for filtering on the values displayed in those columns? I've tried a variety of things but none seem to work

=FINDROW(TransportEmissions, "TransportEmissions[Geography]=%", "Australia")
=FINDROW(TransportEmissions, "TransportEmissions[Geography]=Australia")
=FINDROW(TransportEmissions, "TransportEmissions[Geography][Name]=Australia")

  1. What about if I want to filter on a different column of the "picklisted" table? For instance, the Geography picklist contains an ISO3 reference i.e. AUS for Australia, and it's this which I'd actually like to filter on

I found the problem here: my Geographies table was full of hidden new lines ('\n'), which broke all of the match statements!

=FINDROW(TransportEmissions,"TransportEmissions[Geography]=% AND TransportEmissions[Transport Type]=%", "Australia", "Rail")

Returns the result I expect, addressing point 1 above.

I am still curious if I can rework it to search on the ISO3 field though?

=FINDROW(TransportEmissions,"TransportEmissions[Geography][ISO3]=% AND TransportEmissions[Transport Type]=%", "AUS", "Rail")

doesn't compile

Hi @Arch-0a55

Hello and welcome to the community :slight_smile: .

The following was my attempt to replicate the scenario.
Below is a table I created:
Screen Shot 2021-06-22 at 4.17.00 PM

This screenshot shows the expression which I use to run a query similar to yours on the screen:

I think your query is fine. You may still be experiencing problems due to hidden characters in your data.

I hope this is helpful.

Thanks, this was helpful.

You were quite right - there were newline characters in the column headings, which broke the subreferencing.

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.