Custom invoked function Looping with M query

[Bokchoy]

Hi All,

im trying to create a new column in my table where it only index rows that have [Istradingday] = 1.
Im trying to archieve this by writing a invoked custom fucntion loop shown below:

The current outcome of the custom column looks like this:

I've never used M lanuage before and i need help with the following issues:

1. when [istradingday] = 0, i want to show blank. (currently showing "X")
2. Value + 1 only when [istradingday] = 1. (currently will + 1 for every loop regardless]

side question: is there a way to write for/while loops in M lanuage simuliar to python
  

[lbendlin]

M has built in iteration functions ("each" ), allows recursive calls (as you tried) and it also supports loops (and much more) via List.Generate().  Conceptually it is very unlike Python, so you need to adjust a bit.

Please provide sample data in usable format (not as a picture - maybe insert into a table?) and show the expected outcome.

[Bokchoy]

Hi, thanks for answering my question, please see the attached for example data for 1 week.

Current table

Date	Istradingday	Day	Index	Istradingindex (Desired outcome)
1/08/2021	0	Sunday	1
02/08/2021	1	Mon	2	1
03/08/2021	1	Tues	3	2
04/08/2021	1	Wed	4	3
05/08/2021	1	Thur	5	4
06/08/2021	1	Fri	6	5
07/08/2021	0	Sat	7
...	...	...	...	...

[Anonymous]

Hi @Bokchoy,

For your requirement, you can use the current index value as a condition to filter table records and extract and summary lstradingday field values. (these parts can refer to lbendlin 's formuals)

In my opinion, I think Dax functions should be more suitable for these calculations, M query may spend more resources for these loop calculations. You can try to add buffer functions to reduce the additional resource spends.

Nested functions and Table.Buffer() - Exceed
Regards,

Xiaoxin Sheng

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3sNA3MjAyVNJRMgDi4NK8lMRKIAMkoKAUqxOtZGCEpAaEffPzgKQRmAdWYIymIKQ0tRhIGYNVgVWYoKkIT00BkiZgRWAFpuhGZJQWASlTsCqwCjM0FW5FmUDSDKwIrMAc3SuJJUDSHOKPWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Istradingday = _t, Day = _t, Index = _t, #"Istradingindex (Desired outcome)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Istradingday", Int64.Type}, {"Index", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "IsTradeIndex", each if [Istradingday]=0 then "" else List.Sum(Table.FirstN(#"Changed Type",[Index])[Istradingday]))
in
    #"Added Custom"