powerquery M function to calculate logarithmic average of an text array

[Anonymous]

Dear,

I've been struggling to get a simple function to work.

{36.7 41.1 52.9 43.4 38.3 47.7 34.1 33.3 30.1 33.4 28.2 26.3 27}

This is one example value (text) that is in a column named [Terzen].

All the values need to be converted into a list, secondly multiplied with the power of 10. then averaged, and then taken by a log 10. (logarithmic average of third octave sound levels).

I have tried to add a custom column using the following functions but I have never succeeded to create a working function.

number.log10(list.average(number.power(Text.ToList([Terzen]),10))))

Are there any ideas?

Since i have to use this option a couple of times in this dataset, it is not as easy as to split the list into a table and do the operations in consecutive steps since this will make the code too messy.

BR

Thilo

[Anonymous]

Thanks, that does the calulation. I had to insert one additional step to remove the "{" and "}" and was not able to get it done in one step, but that is ok.

[jaweher899 · Accepted solution]

please try

List.Average(List.Transform(Text.Split([Terzen], " "), each Number.Log10(Number.Power(10, Number.FromText(_)/10))))