Forum Discussion
Solved
Trouble referencing a field
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 |
Hi dof
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
2 Replies
Hi dof
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
works perfectly....thanks Phil!
