Table.First() not returning a row?

[Thomas_Robbs]

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!

[Tomas_Jansson]

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.

[Thomas_Robbs]

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.

[Jason_Tamulonis]

Hi @Thomas_Robbs I just wanted to let you know that we have fixed this issue and I’ve posted some more details here: Update: Formula Improvements - Type understanding

[Markus_Hofmann]

Hi @Jason_Tamulonis, are you sure this is fixed? I’m currently struggling with exactly this and Coda behaves erratically. When I enter the formula it show the correct result. However once I enter/change data in the referenced table, then first/last doesn’t work at all anymore. Any tip on this?

[Jean_Pierre_Traets]

Dear @Markus_Hofmann , welcome to the community

If possible, it will be utmost helpful to share a dummy doc to show what happens

[Markus_Hofmann]

Please have a look in the Projects overview here: First/Last Demo

I’m trying to create an overview of projects.

There is a second table with regular “status updates”, each getting linked to a project. This means this table will contain multiple rows per project.

In the project table I’m trying to show the most recent update for each project. Therefore I’m trying to sort the related updates by date and then only display the .Last(). I tried multiple variations (Desc sorting showing first, asc sorting showing last, placing first/last on row level or on field level, etc.). Nothing kept working reliably.

[Jean_Pierre_Traets]

Dear @Markus_Hofmann,

The main learning from your case is to use Max(), as we are looking for the “biggest” date

[Markus_Hofmann]

Ahhh, thank you so much!