Forum Discussion
Power Query M function - LOOP??
- 9 years ago
You can use List.Accumulate, like in the code below.
Remark: the step values are not really required to calculate the Count, so if you don't need it for something else, you can leave "step" out.
let Source = Table1, CountAndStep = List.Skip(List.Accumulate(List.Transform(Source[number], each if _ = null then 0 else _), {[Count = 0,step = 0]}, (Result, Number) => Result & {if List.Last(Result)[Count] = 0 then [Count = Number, step = 0] else [Count = List.Last(Result)[Count] - Number,step = -1 * Number]})), AddedCountAndStepToSource = Table.FromColumns(Table.ToColumns(Source)&{CountAndStep}), Expanded = Table.ExpandRecordColumn(AddedCountAndStepToSource, "Column3", {"Count", "step"}), NewTableType = Value.ReplaceType(Expanded,Value.Type(Table.AddColumn(Table.AddColumn(Source,"Count",each 0, Int64.Type),"step",each 0, Int64.Type))) in NewTableType - 9 years ago
The example file is empty...
Make sure that you first have your table in Power Query, next it s used in my query (in this case "Table1", adapt to tour table name).
The number must be in column "number" (all lower case).
HI MarcelBeug,
Thanks for helping. I got an error when trying your suggestion. Can you help again?
I am attaching the file so you can have a better picture.
https://drive.google.com/open?id=0B6oYowKqdZzqWlpOSFFWSThQYW8
I believe the error is on step "CountandStep"
The example file is empty...
Make sure that you first have your table in Power Query, next it s used in my query (in this case "Table1", adapt to tour table name).
The number must be in column "number" (all lower case).
- Anonymous9 years agoNot applicable
Sorry!
I wasn't able to work on this until yesterday. The source file was missing, my intetion was to send the code.
Anyway, back to work I found out that my isseu was regarding column type on the source file. I made some adjustments and it is working!!!
thank you MarcelBeug!!
- v-ljerr-msft9 years ago
Microsoft Employee
Hi Anonymous,
Great to hear the problem got resolved! Could you accept the helpful reply as solution to close this thread? :smileyhappy:
Regards