Custom Column - Understanding M language in a sequence

[Theasianmenace]

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(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 text value from a rounded down number from the data value [Food Age in Decimal] which let's say 3.0.

2. 3.0 is divided by 5.0 and returned to largest integer = 3.0/5.0 = .6

3. This number is then multiplied by 5/1.0 = (.6*5)/1 = 3

4. Then concatenated into " Days -"

5. The second part does the same thing except with a value + 5. Essentially if the value is 3.0, the output would be:

0 Days - 5 Days

6. This repeats by 5 (5days - 10days, 10days-15days) and so on until there's no higher value.

Question 1: How does this query know to continue incrementally?

Question 2: How does this math know to drop the 3.0 value into the (0days-5days) bucket?

I'm really confused but would very much appreciate the help and understanding. Even if someone can explain parts of it.

Thanks!

OP Post: https://community.powerbi.com/t5/Desktop/How-to-create-a-values-range-slicer-not-a-time-range/td-p/2...

[Greg_Deckler · Accepted solution]

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.

[Greg_Deckler · Accepted solution]

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.

[Theasianmenace]

Thanks for the explanation! That makes a lot more sense.

[ImkeF · Accepted solution]

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.

[MFelix]

Hi @Theasianmenace,

I believe that a good person to explain you the details of this would be @ImkeF she's a datanaut specialized in M language.

@ImkeF can you please shed some light on this question.

Regards,

MFelix