Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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 ...
  • MarcelBeug's avatar
    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
  • MarcelBeug's avatar
    MarcelBeug
    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).