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

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?