peek function question

[srtgnm]

Hello, I have a bunch of terrible excel tables like this

Product ID	Product name and country	Value
1	Bread	10
	Europe	10
	Great Britain	1
	France	2
	Italy	3
	Germany	4

The list of countries might be different for each Product ID. So, what I do:

if(isnull([Product ID]), 'notAggr', 'Aggr') as [aggr],
if(isnull(Product ID), peek(Product ID), Product ID) as [A2]

The first line is needed to separate this table into two other, where one table would contain Product ID and Product Name, using where function. It works.

The problem is peek function, because it fills only next row, leaving other null. Like this:

Product ID	Product name and country	Value
1	Bread	10
1	Europe	10
	Great Britain	1
	France	2
	Italy	3
	Germany	4

If go peek again in next temporary table, peek again fills lines only partially. So, what I'm doing wrong?

[Vegar · Accepted solution]

You are peeking [Product ID] and the previous [Product ID] is null for row 3-6. Try this instead:

if(isnull([Product ID]), Peek('A2'), [Product ID]) as [A2]

[MikeA]

If I'm understanding correctly you want to copy the Product ID from the previous row down?

Try peek(Product ID,-1)

The second argument tells peek what row to look at, as we want the last row in the table its -1

[MikeA]

Ah yes, Vegar is quite right and caught what I missed 🙂

[srtgnm]

Thank you a lot, that works perfectly.  Spend last 3 days to fix these tables, finally, it's done. 

[Vegar]

Thank you for the feedback @srtgnm . It made me very glad to read it.