Table.First() not returning a row?

#1

Continuing the discussion from .First() not returning the first row in a sorted table:

(and this may also relate to: How to access columns from a formula that returns a row?)

In my example, I have a table of grocery items I need to buy. One column is going to show me the store that I should buy the item at, because in another table, I have all of the prices of all of the groceries and the corresponding store to buy it from.

In this column, I’d like to get the name of the store corresponding to the lowest price I have in the Prices table - something like:

Filter(Prices,Purchase=Item).Sort(true,Price).First().Store

where…

  • Prices is my table of Purchases, each with a Price and Store
  • Item is the name of the item that I need to purchase

My problem here is that First() doesn’t seem to return what I think it should, a single row from the Prices table. As such, I can’t reference “Store” on the row from Prices that is returned, even though looking at the results of First (via hover in the formula editor) shows me the full information about the single row.

What am I missing here? Why can’t I append the column name to First and have it give me the value of the column as I’d expect?

Thanks!

#2

It works fine for me: https://coda.io/d/Demos_dOTCN5QBYBp/_sulo0

If the function is used in a formula you might have to use .value to access the store. Check the coda doc I link to and you should see an example working.

1 Like
#3

Thank you @Tomas_Jansson!

I found a suitable workaround in your solution, but I think the issue I’m highlighting still exists.

First, the workaround…

I updated my formula to be:

=Filter(Prices,Purchase=Item).Sort(true,Price).Store.First()

Notice that I referenced the column “Store” before “First”. This isn’t exactly intuitive for what I wanted to do, but it serves the same purpose, i.e. …

… what I thought I was doing was “Give me a set of rows from Prices for the Item I’m interested in, sorted from lowest to highest price; take the first Price in the set, because that’s the lowest, and give me the Store where that Price was found.”

… but what I have to do is, “Give me a set of rows from Prices for the Item I’m interested in, sorted from lowest to highest price; take the set of Stores from that set of Prices, and give me the first one, because it is associated with the lowest Price.”

In the first approach, I’m expecting First() to return a row from the Table I’m Filter’ing on. Or simply put, Table.First() doesn’t seem to return a Row (which I can then in turn make references to the columns on that Row.

First() says that it “Returns the first item from a list or table.”, but this first item doesn’t seem to be a Row if I’m calling First on a Table.

Side note - it would be great if that documentation provided an example for what to expect from a Table - right now it just gives an example for a List.