Forum Discussion
Custom Column - Understanding M language in a sequence
- 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.
- 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.
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.
- ImkeF8 years agoCommunity Champion
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.
- Theasianmenace8 years agoFrequent Visitor
Thanks for the explanation! That makes a lot more sense.