Forum Discussion

descrt's avatar
descrt
Frequent Visitor
3 years ago
Solved

Need some help with moving average comparison

Hi I am trying to see if each row unit is larger than the average of the preceding last 6 points but I keep running into errors.

 

Can anyone please help to see what is wrong with my code?

 

Thank you!

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQitWJVjIyhlBGYMoYQplAKEMUQXOIBmMTMGVmDlECNcYUSMUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [units = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"units", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
PrevWeeks = Table.AddColumn(#"Added Index", "PrevWeeks", each List.Range(#"Added Index"[units], [Index] - 6, 6)),
AverageValue = Table.AddColumn(PrevWeeks, "AverageInvValue", each List.Average((PrevWeeks[units]))),
CompareValue = Table.AddColumn(AverageValue, "ExceedsInvAverage", each [units] > ([AverageValue] * 1.2))
in
CompareValue

Need Help
  • johnt75's avatar
    johnt75
    3 years ago
    let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQitWJVjIyhlBGYMoYQplAKEMUQXOIBmMTMGVmDlECNcYUSMUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [units = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"units", type number}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
PrevWeeks = Table.AddColumn(#"Added Index", "PrevWeeks", each List.Range(#"Added Index"[units],
    if [Index] < 6 then 0 else [Index] - 6, if [Index] < 6 then [Index] +1 else 6)),
AverageValue = Table.AddColumn(PrevWeeks, "AverageInvValue", each List.Average(([PrevWeeks]))),
CompareValue = Table.AddColumn(AverageValue, "ExceedsInvAverage", each [units] > ([AverageInvValue] * 1.2))
in
CompareValue

    You need to change the type of [units] to be a number, not text. I've added some error checking so that if the index is less than 6, i.e. it cannot go back 6 rows, it will get as many as it can get.

    Also, the column reference to AverageValue should be AverageInvValue and you need to reference the PrevWeeks column when doing the average.

2 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago
    let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQitWJVjIyhlBGYMoYQplAKEMUQXOIBmMTMGVmDlECNcYUSMUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [units = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"units", type number}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
PrevWeeks = Table.AddColumn(#"Added Index", "PrevWeeks", each List.Range(#"Added Index"[units],
    if [Index] < 6 then 0 else [Index] - 6, if [Index] < 6 then [Index] +1 else 6)),
AverageValue = Table.AddColumn(PrevWeeks, "AverageInvValue", each List.Average(([PrevWeeks]))),
CompareValue = Table.AddColumn(AverageValue, "ExceedsInvAverage", each [units] > ([AverageInvValue] * 1.2))
in
CompareValue

    You need to change the type of [units] to be a number, not text. I've added some error checking so that if the index is less than 6, i.e. it cannot go back 6 rows, it will get as many as it can get.

    Also, the column reference to AverageValue should be AverageInvValue and you need to reference the PrevWeeks column when doing the average.