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).
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
NewTableTypeHI 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"
- MarcelBeug9 years ago
Community Champion
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
- v-ljerr-msft9 years ago
Microsoft Employee
Hi Anonymous,
Did the problem get resolved? If yes, could you accept the helpful reply as solution to close this thread?
If you still have any question on this issue, feel free to post here. :smileyhappy:
Regards