Looping through a table in an m function

[Anonymous]

I have a function that takes in a table name, and I want to loop through the 2 fields in the table, and create a string that has a comma between the 2 fields for a line and a new line for each row. Each of the values would go through some calculations before being added to the string. I need to know how to do a loop that will loop through a table (with no index).

Here is the file, look at the fn_basic_line_chart function.

Edit: Here is a more in depth look at what I am trying to do, note that the calculations will be WAY more complex in the real life situation, but this is the loop I am looking for.

So I want to loop through a table in a function. Let’s call this function fn_my_function and at the moment it looks like this (I am stripping out anything we don’t need):

(lineData as table)=>
let
  Source = “Something”
in
  Source

So at this point, the function brings in a table as the variable lineData, then just returns the text string “Something”. I want to loop through the table and process the data (including some calculations) into a string. Lets say we have this table sent in:

Year	Price
2000	55
2001	57
2002	50

Now once I loop through and process it, lets say we subtract 1000 of the year and double the price and return a string with a comma between the year and price, and a new line for each new row. So we would return a string that looks like:

1000,110
1001,114
1002,100

And the function would become something like (though not the correct syntax):

(lineData as table)=>
let
  Source = “” // blank text string
  Foreach(row in lineData) //loop through each row on the table
  {
    If row <> 1 //check if it is not the first row
      Source = Source & “
“ // add a new line if it is not the first row
    Source = Source & row[Year]-1000 & “,” //add the year after subtracting 1000 as well as the comma after
    Source = Source & row[Price]*2 //add the price after doubling it
  } //end the loop
in
  Source

So now we return the string we have created that should look like the one above. However, I am not sure how to loop through a table (the foreach is more how it is done in php, I am not sure how it is done in power query).

[d_gosbell]

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]))

[Anonymous]

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!

[d_gosbell]

---

@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.

[Anonymous]

---

@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.

[d_gosbell · Accepted solution]

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
    result

PS. The Binary.FromText in the first line is just the pasted data from your question

[Anonymous]

Awesome! I just made Source=lineData and it all worked as expected. I will look into some of the code there and work out how it all works so I can pull it apart and use it where needed. I notice that even though we added a column here, it does not show in the table we added it to. Is this because the added column is only on the instance of the table in this function (so the variable of Source), so once the function completes it ceases to exist?

[d_gosbell]

---

I notice that even though we added a column here, it does not show in the table we added it to. Is this because the added column is only on the instance of the table in this function (so the variable of Source), so once the function completes it ceases to exist?

---

Yes, that's correct. The table with the added column is sort of like a tempory variable that only exists for the duration of the query. Note that it's also possible that the optimizer in PowerQuery could "fold" the logical addcolumns and accumulate steps into one physical loop over the source data (it's a pretty smart engine most of the time)

[Anonymous]

Another quick question, I send a table in as (thisTable as table)=> is it possible to send a field in that way? So in this case I have (thisTable as table, field1 as field, field2 as field)=> and would send in Year Prices as the table name, Year as field1 and Price as field2.

Or would I have to send in the field names as text and reference them that way?

[d_gosbell]

---

@Anonymous wrote:

Another quick question, I send a table in as (thisTable as table)=> is it possible to send a field in that way? So in this case I have (thisTable as table, field1 as field, field2 as field)=> and would send in Year Prices as the table name, Year as field1 and Price as field2.

Or would I have to send in the field names as text and reference them that way?

---

I think you'd have to send in the field names as text. Checking out the M type system here I can't see any way of strongly typing to a field. So you'd probably need to write the code as something like the following:

let fnAccumulate = (sourceTable as table,field1 as text, field2 as text ) as text =>
let
    #"Added Custom" = Table.AddColumn(sourceTable, "Custom", each Number.ToText(Record.Field(_,field1) - 1000) & "," & Number.ToText( Record.Field(_,field2) *2)),
    Custom = #"Added Custom"[Custom],
    result = List.Accumulate(Custom, "", (state, current) => state & current & "#(cr)#(lf)"  )
in
    result
in 
   fnAccumulate

[Anonymous]

Yeh, thats what I did, just figured it would be cool to have a selector like with the table as it saves typos. Thanks for that. I also wanted to have a whole number come in, but params only support decimal (number) and no other types! Have to convert it once in there to int64. This seem right?

[d_gosbell]

---

@Anonymous wrote:

Yeh, thats what I did, just figured it would be cool to have a selector like with the table as it saves typos. Thanks for that. I also wanted to have a whole number come in, but params only support decimal (number) and no other types! Have to convert it once in there to int64. This seem right?

---

Yes, that sounds correct. The base type system only has "number", it does not differentiate between decimal or int or anything like that.