Assigning different minimum values to a column based on existing index in Power Query

Question

I am working in Power Query and have a month and index column with roughly 20,000 rows. I am trying to assign a first instance number to each unique item that is equal to the minimum index number associated with each item across multiple rows.

There are many 1000's of distinct item numbers and an index range of 1-12. The desired output would be in a 3rd column. I am trying to attach this to the data by creating a custom column that displays this result. There are many other columns in the dataset that should remain unaffected.

enter image description here

I have tried a combination of List.Min and Table.Selectrow formulas but can not seem to reach the desired result. Any feedback on this would be much appreciated!

Answer 1

you can try this

= Table.AddColumn(#"Changed Type", "First Instance", each List.Min(Table.SelectRows(#"Changed Type",(x)=>x[ITEM]=[ITEM])[MONTH INDEX]))