Repeat steps multiple times over the same table

[donpep0]

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:

Date	Index	Volume
9/01	1	1100
9/02	2	1195
9/03	3	139
9/04	4	1013
9/05	5	680
9/06	6	663
9/07	7
9/08	8
9/09	9

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:

Date	Index	Volume	avg
9/01	1	1100	null
9/02	2	1195	null
9/03	3	139	null
9/04	4	1013	null
9/05	5	680	null
9/06	6	663	null
9/07	7		623.75
9/08	8		null
9/09	9		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

Date	Index	volume
9/01	1	1100
9/02	2	1195
9/03	3	139
9/04	4	1013
9/05	5	680
9/06	6	663
9/07	7	623.75
9/08	8
9/09	9

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

[KT_Bsmart2gethe]

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

[wdx223_Daniel]

= #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]

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