Forum Discussion

donpep0's avatar
donpep0
Frequent Visitor
3 years ago

Repeat steps multiple times over the same table

Hi all!

 

I'm a little stuck whit power Query, I'm trying to repeat multiple steps over the same table.

My input(Source) looks like this:

DateIndexVolume

9/01

1

1100
9/0221195
9/033139
9/0441013
9/055680
9/066663

9/07

7

 
9/088 
9/099 

 

I want to fill the empty rows with the average of the last 4 periods.

So the volume for index 7 will be the volume average of the Index3,4,5,6,

and the volume for index 8 will be the volume average of Index 4,5,6,7, and so on until the last index.

 

To calculate the volume for index 7, I'm applying the below steps:

col_avg = Table.AddColumn(Source, "avg", each if [Index] = last_index+1 then List.Average(List.Select(List.Range(Source[volume], _[Index]-5,4), each _<>null)) else null, type number)
The last_index is the last index with volume data, in this case, will be 6. I calculated it previously and I stored it in a variable named last_index.

Now, my table looks like this:

DateIndexVolumeavg

9/01

1

1100null
9/0221195null
9/033139null
9/0441013null
9/055680null
9/066663null

9/07

7

 623.75
9/088 null
9/099 null

 

And now I'm creating a new column to merge the volume column with the avg column

 

col_avg_1 = Table.AddColumn(col_avg, "volume", each if [avg] = null then [Volume] else [avg]

 

then I remove the avg column and the Volume column:

#"Removed Columns" = Table.RemoveColumns(col_avg1, {"Volume", "avg"}

 

And now I have this output

DateIndexvolume

9/01

1

1100
9/0221195
9/033139
9/0441013
9/055680
9/066663

9/07

7

623.75
9/088 
9/099 

 

I have to apply the above steps to calculate the next periods over the same table.

 

Do you know how can I create a loop to repeat the above steps n times?

 

Thanks

Need Help

3 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago

    = #table(Table.ColumnNames(PreviousStepName),List.Accumulate(Table.ToRows(PreviousStepName),{{},{}},(x,y)=>{x{0}&{{y{0},y{1},y{2}??List.Average(List.LastN(x{1},4))}},x{1}&{y{2}}}){0})

  • jbwtp's avatar
    jbwtp
    Memorable Member
    3 years ago

    Hi donpep0,

     

    You can try something like this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstT1SsxT0lEyBGFDAwOlWB2QoFtqElDACCxoaQoV9E0sAgoYgwSNLaFijgUgMROQmIGhMVxhJVDAFIjNLGAmepWCrDEDYTNjuFgOkG8OxDDTStOBHAuEQHBqAZBjCRaIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Index = _t, Volume = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Index", Int64.Type}, {"Volume", Int64.Type}}),
    Calculate = List.Accumulate(Table.ToRecords(#"Changed Type"), {[lst = {}]}, (a, n)=> 
        let 
        last = List.Last(a)[lst],
        updateVolume = if n[Volume] = null then Record.TransformFields(n, {"Volume", each List.Average(last)}) else n,
        updateLst = if List.Count(last) = 4 then List.Skip(last) else last,
        addLst = Record.AddField(updateVolume, "lst", updateLst & {updateVolume[Volume]})
        in a & {addLst}),
    Output = Table.FromRecords(List.Skip(Calculate), Value.Type(#"Changed Type"))
in
    Output

     

    Kind regards,

    John

  • KT_Bsmart2gethe's avatar
    KT_Bsmart2gethe
    Impactful Individual
    3 years ago

    Hi donpep0 ,

     

    An alternate approach to the situation. I am using Replace.Value instead of List.Accumulate.

    Also, I don't use the index column.

     

    Starting Point:

     

    Code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NczBDQAhCETRVjacTRRRlFqM/bexgJnDvzwmnENcrfZOhTji1uiWQx2aF7aZKlAJFUscwBHYWFIndHq631cFaqRvuYDLS9mQ7X1JBrJH9wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Index = _t, Volume = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Index", Int64.Type}, {"Volume", Int64.Type}}),
    
    //Replace null with average of previous four periods
    #"Replaced Value" = Table.ReplaceValue(
                            #"Changed Type", 
                            each null, 
                            each 
                                let 
                                    LastFourPeriods = Table.SelectRows(
                                                            #"Changed Type", 
                                                            (x)=> x[Date] < [Date] and x[Date] >= Date.AddDays([Date], -4)
                                                        )[Volume], 
                                    FourPeriodAverage = List.Average(
                                                            List.RemoveNulls(LastFourPeriods)
                                                        ) 
                                in 
                                    FourPeriodAverage, 
                            Replacer.ReplaceValue,
                            {"Volume"}
                        )
in
    #"Replaced Value"

     

    Outcome:

     

     

    Regards

    KT