none
Averaging data RRS feed

  • Question

  • I have records which comprise of a number of fields e.g.

    Code, Amount1, Amount2, Amount3, Amount4...

    I need to average the amount for each Code record.  The problem is that when I import the data I don't know how many Amounts there might be.  So I can't use List.Average([Amount1],[Amount2]...,[AmountN])

    I do have a list of the column headings that I need to average e.g.
    FieldsToAverage = {"Amount1","Amount2","Amount3" ..."AmountN}

    Is there any way of converting the FieldsToAverage list so it can be used as the parameter to List.Average?

    If not, is there another way of doing it? If it is any help I also know, for each data file what N will be. 

    Unpivoting the data then doing a List.Accumulate, a List.Count, dividing one by the other and then merging that back into the original Table will work - but that is so horribly messy, that I feel there must be a better way.

    Thanks

    Malcolm


    Saturday, July 27, 2019 11:12 PM

Answers

  • Hi Malcolm,

    Recently there was similar thread, where Bill Szysz provided elegant solution. If you already handle to make FieldsToAverage dynamic, just add such step:

    = Table.AddColumn(YourTable, "Average", each List.Average(Record.ToList(Record.SelectFields(_, FieldsToAverage))))
    • Edited by Aleksei Zhigulin Sunday, July 28, 2019 11:12 AM
    • Marked as answer by MaBacc Sunday, July 28, 2019 3:23 PM
    Sunday, July 28, 2019 11:05 AM
  • Thanks.  I was very nearly there when I tried something that, but it would have taken me ages to work out what I wasn't doing right.  So many thanks.
    • Marked as answer by MaBacc Sunday, July 28, 2019 3:09 PM
    Sunday, July 28, 2019 3:09 PM

All replies

  • Hi Malcolm,

    Recently there was similar thread, where Bill Szysz provided elegant solution. If you already handle to make FieldsToAverage dynamic, just add such step:

    = Table.AddColumn(YourTable, "Average", each List.Average(Record.ToList(Record.SelectFields(_, FieldsToAverage))))
    • Edited by Aleksei Zhigulin Sunday, July 28, 2019 11:12 AM
    • Marked as answer by MaBacc Sunday, July 28, 2019 3:23 PM
    Sunday, July 28, 2019 11:05 AM
  • Thanks.  I was very nearly there when I tried something that, but it would have taken me ages to work out what I wasn't doing right.  So many thanks.
    • Marked as answer by MaBacc Sunday, July 28, 2019 3:09 PM
    Sunday, July 28, 2019 3:09 PM