Forum Discussion
Looping through a table in an m function
So given the edits to your question I would do this in two steps, first doing an add column to produce the row by row operation where you subtract 1000 from the year and multiply the price by 2. Then do a List.Accumulate over that column adding line breaks between each row.
eg
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMFDSUTI1VYrVAfMMQTxzGM8IxDNQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, Price = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Price", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Number.ToText([Year] - 1000) & "," & Number.ToText( [Price]*2)), Custom = #"Added Custom"[Custom], result = List.Accumulate(Custom, "", (state, current) => state & current & "#(cr)#(lf)" ) in resultPS. The Binary.FromText in the first line is just the pasted data from your question
Table.AddColumn will have the same effect as looping through each row and applying logic to generate a new column. You can either use one of the options in the "Add Column" ribbon in the Query Editor (there is even a button there that will allow you to call an existing custom function). Or you could type in the expression manually.
eg.
= Table.AddColumn(#"Changed Type", "Custom", each Number.ToText( [Year]) & "," & Number.ToText([Price]))
But I am not trying to add a column, I am not in any way modifying the source table. I just want the values so I can create a string out of them after doing some calculations (the one string for the entire table). Seems kind of weird that nobody seems to know how to loop through a table, I would have thought that would be one of the most important functions in power query!
Anonymous wrote:But I am not trying to add a column, I am not in any way modifying the source table. I just want the values so I can create a string out of them after doing some calculations (the one string for the entire table). Seems kind of weird that nobody seems to know how to loop through a table, I would have thought that would be one of the most important functions in power query!
It would probably have been easier to understand if you provided a few rows of example data and the output you were after. I though you wanted to produce a new string per row. But if you want to produce a single string after looping through all the rows in a table then you could look at calling Table.ToList then using List.Accumulate
Reza has a great post on List.Accumulate here including a number of examples one of which shows using it to do a concatenate operation.
d_gosbell wrote:
It would probably have been easier to understand if you provided a few rows of example data and the output you were after.I have now edited the original post to include a lot more detail, including this.
d_gosbell wrote:Reza has a great post on List.Accumulate here including a number of examples one of which shows using it to do a concatenate operation.
I have had a read of this page and this may work for me, I will look further into it. Thanks.
So given the edits to your question I would do this in two steps, first doing an add column to produce the row by row operation where you subtract 1000 from the year and multiply the price by 2. Then do a List.Accumulate over that column adding line breaks between each row.
eg
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMFDSUTI1VYrVAfMMQTxzGM8IxDNQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, Price = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Price", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Number.ToText([Year] - 1000) & "," & Number.ToText( [Price]*2)), Custom = #"Added Custom"[Custom], result = List.Accumulate(Custom, "", (state, current) => state & current & "#(cr)#(lf)" ) in resultPS. The Binary.FromText in the first line is just the pasted data from your question