Trouble referencing a field

[dof]

So I have a table such as the one below which I call tblCodes

I want to Group By the distinct Codes, and in my aggregation get the position number(s) of each Code (in a list that I made.)

This is my code...

let
    Source = Excel.CurrentWorkbook(){[Name="tblCodes"]}[Content],
    mytable = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}}),
    List_CODES=List.Buffer(mytable[Code]),
    Results= Table.Group(mytable, {"Code"}, {{"mylist", each List.PositionOf(List_CODES,[Code],Occurrence.All),type list}})
in
    Results

Now it doesn't give me any errors, but my lists are all empty.  However if I replace [Code] with a constant e.g. 1, then all my lists are filled with the positions of the value 1.  (In the example below 0,3).  So it would seem that I'm not currently able to pick up the value of [Code].  What am I doing wrong?

Thanks!!

Code
1
13
15
1
13
8
13

[PhilipTreacy · Accepted solution]

Hi @dof 

Download sample PBIX file

Try this instead

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1QGSxhDKFEIhi1nAObEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t]),
    mytable = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}}),
    #"Grouped Rows" = Table.Group(mytable, {"Code"}, {{"Grouped Code", each _, type table [Code=nullable number]}}),
    List_CODES = List.Buffer(mytable[Code]),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.PositionOf(List_CODES, [Code], Occurrence.All)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Grouped Code"})
in
    #"Removed Columns"

Regards

Phil

[dof]

works perfectly....thanks Phil!