Forum Discussion

Theasianmenace's avatar
Theasianmenace
Frequent Visitor
8 years ago
Solved

Custom Column - Understanding M language in a sequence

Hi all,   I'm trying to understand this query from another post so that I can expand on it. May I ask someone to follow my math and correct me if I am failing to understand something?   Text.From...
  • Greg_Deckler's avatar
    8 years ago

    Text.From(Number.RoundDown([Food Age in  Decimal]/5.0,0)*5/1.0)
    & " Days - "
    & Text.From(((Number.RoundDown([Food Age in Decimal]/5.0,0)*5.0+5.0)/1.0))
    & " Days"

     

    Written out, this formula does:
    1. Takes a numeric value [Food Age in Decimal] and divides by 5 and then rounds the number down. If 3.0, that would become 0. .6 rounded down is 0. This 0 is then multiplied by 5 and divided by 1 still making it zero. This numeric value is converted to Text and then the text string " Days - " is concatenated so now you have "0 Days - "

    2. The same numeric value (3.0) is now divided by five and rounded down again, obtaining 0 again. Multiplying this 0 by 5 results in 0 then +5 makes the number 5 and then dividing this by 1.0 still leaves you with 5. This numeric 5 is converted to text and then " Days" is concatenated to it and then the whole thing is appended to the previous string so now you have "0 Days - 5 Days".

    3. This becomes the value of the column.

     

    There is no repeating with this in that formula other than this executes for every row in the table. So for every row, the numeric value in [Food Age in Decimal] is taken for that row and the above calculations ran.

     

  • ImkeF's avatar
    ImkeF
    8 years ago

    Just to add on the good things Greg_Deckler said:

     

    If you look a the full formula generated by the Table.AddColumn-step you will notice that there is an "each"-keyword before that formula, like:

     

    Table.AddColumn(YourPreviousStepname, "YourNewColumnName", each Text.From(Number.RoundDown([Food Age in  Decimal]/5.0,0)*5/1.0) & " Days - " & Text.From(((Number.RoundDown([Food Age in Decimal]/5.0,0)*5.0+5.0)/1.0)) & " Days")

     

    This makes the 3rd parameter a function itself, that, like Greg mentioned, will be applied to every row in the table.