Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Looping through a table in an m function

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. E...
Need Help
  • d_gosbell's avatar
    d_gosbell
    7 years ago

    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

d_gosbell's avatar
d_gosbell
Super User
7 years ago
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's avatar
Anonymous
Not applicable
7 years ago
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's avatar
    d_gosbell
    Super User
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        d_gosbell
        Super User
        7 years ago
        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)