Power Query M function - LOOP??

[Anonymous]

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

[MarcelBeug · Accepted solution]

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

[MarcelBeug · Accepted solution]

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

[Anonymous]

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"

[v-ljerr-msft]

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. 

Regards

[MarcelBeug · Accepted solution]

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).

[Anonymous]

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-msft]

Hi @Anonymous,

Great to hear the problem got resolved! Could you accept the helpful reply as solution to close this thread? 

Regards