Excel 365 Question

[Doofus]

I'm updating my Excel skills to accommodate new features added in 365. Power Query in particular.

I think I know the answer to this ("No"), but I'm going to ask anyway.

I have a table, and as new rows are added , I need to manually update one field. Let's say there's a column called "Serial number", and one called "Comment", and I manually add a comment (or not) against each serial number.

As the table updates and more rows are added, can I get Excel to auto-populate new "Comment" cells if the serial number (and my manual comment) have appeared in the table previously?

I know I could use another table of serial numbers and comments, and get Excel to pick from that, but I'm wondering if there's some AI logic or somesuch that will just say "This serial number's been entered before, and the comment against it was "X", so I'll put the same comment against it this time too.

Can I do it during the Transfom Data stage of PQ?

[Sporky]

Could that be done with an XLookup?

[Doofus]

Sporky said:

Could that be done with an XLookup?

Only if I create a separate list of numbers and comments and, whilst I can do that, I'm hoping there's a slicker way.

The issue with a lookup is that if the serial number is unique, there will be no comment added automatically but I might add one manually, and that will overwrite the lookup formula.

[Sporky]

Doofus said:

The issue with a lookup is that if the serial number is unique, there will be no comment added automatically but I might add one manually, and that will overwrite the lookup formula.

I might have misunderstood, but that'd be the first entry for that serial number, so OK to overwrite the formula. If the serial number appears later it'd pick up that earlier entry.

You want the lookup to look at the cells from the top to the tow above it, so something like $A$2:$A9 (in the 10th row, assuming serial numbers are in A) as the range to search, and the same but substituting the comment column letter as the range to return.

Unless you might put the serial number into a higher row.

[Doofus]

Sporky said:

I might have misunderstood, but that'd be the first entry for that serial number, so OK to overwrite the formula. If the serial number appears later it'd pick up that earlier entry.

I know, but having a column with some text and some formulas feels like a messy compromise.

Sporky said:

You want the lookup to look at the cells from the top to the tow above it, so something like $A$2:$A9 (in the 10th row, assuming serial numbers are in A) as the range to search, and the same but substituting the comment column letter as the range to return.

Unless you might put the serial number into a higher row.

Again, yes, but this is a table so I'm not using cell references.

[Doofus]

Once I've gone through the ballache of populating the 200-odd rows currentky in the table, each month when I refresh the source data, I only expect to add another dozen or twenty rows, so adding the comments manually isn't impossible, but if I can automate it, I'd like to.

[balham123]

When you start typing in a cell, doesn't it automatically start offering completions based on other content in the column?

[Sporky]

Doofus said:

Again, yes, but this is a table so I'm not using cell references.

You can mix, no?

But I take your point about messiness.

[Doofus]

balham123 said:

When you start typing in a cell, doesn't it automatically start offering completions based on other content in the column?

Yes, but I want it to know what to put in the cell without me typing anything, unless there's a serial number it hasn't encountered before.

[surveyor]

CHatGPT is made for this sort of question - excel stuff is really where it excels. Just be careful what info you put in - I'm allowed to put generic stuff in to help get answers, but sensitive info for instance.

Answer to your question above

Short answer: you can absolutely auto-populate—but not by “remembering” inside Power Query. PQ is stateless: it won’t recall what you typed last refresh. You need a keyed source of truth for comments (even if it lives in the same workbook), then merge it in. Here are practical ways to do it:

1. What works (and what doesn’t)

• **Power Query cannot “learn” or keep memory** of past outputs. No built-in AI matching there.
• **Best-practice:** keep a tiny **Comments** table (SerialNumber → Comment). Merge it onto your incoming data in PQ.
• If you really don’t want a separate table, you can do a **formula-only approach** that looks “upwards” in the same table to reuse a previous comment for the same serial number.

---

1. Option A (recommended): Merge a Comments table in Power Query

1. In Excel, create a small table (e.g., `Comments`) with columns:

* `Serial number`
* `Comment`
* (Optional but useful) `CommentDate` or `Version` if comments can change.

2. In **Power Query**:

* Load your incoming table (call it `Fact`).
* Load the `Comments` table as a separate query.
* (Optional) If there can be multiple comments per serial, “**Group By**” the `Comments` table on `Serial number` and keep the **latest** comment (use `Max` on `CommentDate`, then merge back to get that row).
* **Merge** `Fact` → `CommentsLatest` on `Serial number` with a **Left Outer** join.
* **Expand** to bring in `Comment`.

That’s it. Every refresh, any new serial number that already exists in `Comments` gets its comment pulled in automatically.

1. # M snippet (illustrative)

```m
let
Source = Excel.CurrentWorkbook(){[Name="Fact"]}[Content],
Fact = Table.TransformColumnTypes(Source,{{"Serial number", type text}}),

CommentsSrc = Excel.CurrentWorkbook(){[Name="Comments"]}[Content],
Comments = Table.TransformColumnTypes(CommentsSrc,{
{"Serial number", type text}, {"Comment", type text}, {"CommentDate", type datetime}
}),
// keep the latest comment per serial (drop this section if you only ever have one row per serial)
Grouped = Table.Group(Comments, {"Serial number"},
{{"Latest", each Table.Max(_, "CommentDate"), type table [Serial number=nullable text, Comment=nullable text, CommentDate=nullable datetime]}}),
CommentsLatest = Table.ExpandTableColumn(Grouped, "Latest", {"Comment"}, {"Comment"}),

Merged = Table.NestedJoin(Fact, {"Serial number"}, CommentsLatest, {"Serial number"}, "C", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Merged, "C", {"Comment"}, {"Comment"})
in
Expanded
```

> Where do you put new comments you type by hand?
>
> Do **not** type them into the PQ output table (they’ll be wiped on refresh). Instead, type or paste new/updated comments into the `Comments` table (your source of truth). PQ will then merge them in every time.

• Tip:** Normalize the join key—trim, set to upper/lower, remove spaces—to avoid mismatches:

```m
WithKey = Table.TransformColumns(Fact, {{"Serial number", each Text.Upper(Text.Trim(_)), type text}});
```

Do the same on the `Comments` query.

---

1. Option B: Formula-only auto-fill inside the sheet

If you insist on one table and want Excel to “reuse” a prior comment for the same serial when you add a new row, you can use a formula in the `Comment` column that looks for the most recent earlier row with the same serial:

Assume your table is `Tbl` with columns `Serial number` and `Comment`. Put this **in the Comment column** (as a calculated column):

```excel
=IF(
[@Comment]<>"",
[@Comment],
IFERROR(
LOOKUP(
2, 1/((Tbl[Serial number]=[@[Serial number]])*(ROW(Tbl[Serial number])<ROW())),
Tbl[Comment]
),
""
)
)
```

• If you type a comment, it stays.
• If you leave it blank, it grabs the **last** earlier comment for that serial (if any).
• Caveat: formulas referencing the same column can be finicky; structured refs + `ROW()` work fine for many, but test on your dataset. Also, this won’t help if your “previous” comment is only present in a refreshed PQ output (because refresh overwrites the column). It’s best when your table is manual or the column is formula-driven.

---

1. Option C: Office Scripts / Power Automate (advanced)

• You can keep comments in a separate sheet and use a **flow** to:

1. On refresh (or on new row), look up the serial in the comments sheet,
2. Write the comment into the output table.

• This gives you more control (e.g., prompts if multiple past comments exist), but it’s more setup than a simple PQ merge.

---

1. Bottom line

• **During Transform Data (PQ)**: Yes—**via a Merge** with a comments table. PQ won’t auto-infer or remember prior rows by itself.
• **If you want zero extra tables**: use the **formula approach** that copies a previous comment for the same serial on insert—but avoid mixing it with PQ overwriting that column on refresh.

If you’d like, tell me your current layout (names of your queries/tables and whether comments can change over time), and I’ll tailor a drop-in M script and workbook structure for you.

[Doofus]

Thank you. That involves keeping a(nother) table, containing serials and comments, which is what I was hoping to avoid.

I have concluded that, without a but of VBA, it's not possible. I'm trying to create something robust that several people can use, but this is one element that I can't open up to them all.

Thanks all.

[jagnet]

Is Excel a strict requirement? You could easily do this in Google Sheets with a custom function if that's an option.

[Doofus]

jagnet said:

Is Excel a strict requirement? You could easily do this in Google Sheets with a custom function if that's an option.

I've used Excel for pever and, as I said, it's my Excel skills I'm brushing up on.

I could use sheets, but I don't want to.

[jagnet]

That's fair enough

[surveyor]

Doofus said:

Thank you. That involves keeping a(nother) table, containing serials and comments, which is what I was hoping to avoid.

I have concluded that, without a but of VBA, it's not possible. I'm trying to create something robust that several people can use, but this is one element that I can't open up to them all.

Thanks all.

Did you not read Option B above?

[Doofus]

surveyor said:

Doofus said:

Thank you. That involves keeping a(nother) table, containing serials and comments, which is what I was hoping to avoid.

I have concluded that, without a but of VBA, it's not possible. I'm trying to create something robust that several people can use, but this is one element that I can't open up to them all.

Thanks all.

Did you not read Option B above?

I'm creating a table. Excel won't auto-populate a table with a formula. Plus, as I said, that method would result on a column containing a mix of text and formulae, and that's messy, so I don't want to do it.

My question was "As the table updates and more rows are added, can I get Excel to auto-populate new "Comment" cells if the serial number (and my manual comment) have appeared in the table previously?", and the answer is "No".

This is not a problem I can't solve. I wanted to know of there was a slicker way of doing it.