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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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?