Forum Discussion
Power Query M function - LOOP??
Hi,
I need to do some ETL with M function. I'm still learnig how to deal with it. Can anybody help me?
I Need to create two adittional columns [count] and [step] and fill these columns based on Rules bellow. How can I do this on power query?
thank you all
| Data | number | Rules | Count | step |
| Volume de Utilização | 1 | if 'First Record or previous [count] = 0 --> [Count]= [number] Step=0 | 1 | 0 |
| Negócio | 1 | If previous count<>0; step = - [number] ; [count] = previous [count] + step | 0 | -1 |
| 0 | 0 | |||
| Notebook básico | 2 | 2 | 0 | |
| xxxxxxxxxxx | 1 | 1 | -1 | |
| yyyyyyyyyyy | 1 | 0 | -1 | |
| Acrobat Professional por equipamento | 1 | 1 | 0 | |
| xxxxxxxxxxx | 1 | 0 | -1 | |
| Project Professional por equipamento | 1 | 1 | 0 | |
| xxxxxxxxxxx | 1 | 0 | -1 |
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 NewTableTypeThe 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).
6 Replies
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"
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).