power query m | list.average (selection)

[acol]

Hello all,
Complete new to this, but trying to figure it out

I have this code/:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each let
last4ColumnValues = List.LastN(Record.FieldValues(_),4),
result = List.Average(List.Select(last4ColumnValues, each _<>0))
in
result)

What I need is to be able to select the different columns for which i want the average calculated...
and those aren't the last N columns prior to this one.

Actually I 'simply' need a power query alternative for =AVERAGEIF(B2:B5,">0")

Have been looking and puzzling for some hours now, but I raise the white flag...
Can anyone help me out? Would be very much appreciated.

 

[Bo_Ry]

Try this where {"Header1","Header2"} is list for field header

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],[/I]
add = Table.AddColumn(Source, "Custom", each List.Average(List.Select(Record.FieldValues(Record.SelectFields(_,{"Header1","Header2"})), each _>0 )) )
in
add

 

[acol]

List.Average(List.Select(Record.FieldValues(Record.SelectFields(_,{"Header1","Header2"})), each _>0 )) )

Hoi, there
Thanks for your feedback. Code above seems to generate error.
To be sure: hereunder the complete code I used:

let
Source = Odbc.DataSource("dsn=BTC", [HierarchicalNavigation=true]),
btc_Database = Source{[Name="btc",Kind="Database"]}[Data],
btcmobile_inf_View = btc_Database{[Name="btcmobile_inf",Kind="View"]}[Data],
#"Renamed Columns" = Table.RenameColumns(btcmobile_inf_View,{{"TrafM-1", "usCM-01"}, {"TrafM-2", "usCM-02"}, {"TrafM-3", "usCM-03"}, {"TrafM-4", "usCM-04"}, {"TrafM-5", "usCM-05"}, {"TrafM-6", "usCM-06"}, {"TrafM-7", "usCM-07"}, {"TrafM-8", "usCM-08"}, {"TrafM-9", "usCM-09"}, {"TrafM-10", "usCM-10"}, {"TrafM-11", "usCM-11"}, {"TrafM-12", "usCM-12"}, {"orCurrentstatus", "orSTS"}}),
#"add usTOTL03M" = Table.AddColumn(#"Renamed Columns", "usTOTL03M", each [#"usCM-03"]+[#"usCM-02"]+[#"usCM-01"]),
#"add usTOTL06M" = Table.AddColumn(#"add usTOTL03M", "usTOTL06M", each [#"usCM-06"]+[#"usCM-05"]+[#"usCM-04"]+[#"usCM-03"]+[#"usCM-02"]+[#"usCM-01"]),
#"add usTOTL12M" = Table.AddColumn(#"add usTOTL06M", "usTOTL12M", each [#"usCM-12"]+[#"usCM-11"]+[#"usCM-10"]+[#"usCM-09"]+[#"usCM-08"]+[#"usCM-07"]+[#"usCM-06"]+[#"usCM-05"]+[#"usCM-04"]+[#"usCM-03"]+[#"usCM-02"]+[#"usCM-01"]),
#"usAVL3M" = Table.AddColumn(#"add usTOTL12M", "usAVL3M", each List.Average(List.Select(Record.FieldValues(Record.SelectFields(_,{[#"usCM-01"],[#"usCM-02"]})), each _>0 )))
in
#"usAVL3M"

 

[Bo_Ry]

remove square brackets and hash sign

#"usAVL3M" = Table.AddColumn(#"add usTOTL12M", "usAVL3M", each List.Average(List.Select(Record.FieldValues(Record.SelectFields(_,{"usCM-01","usCM-02"})), each _>0 )))

 

[acol]

remove square brackets and hash sign

#"usAVL3M" = Table.AddColumn(#"add usTOTL12M", "usAVL3M", each List.Average(List.Select(Record.FieldValues(Record.SelectFields(_,{"usCM-01","usCM-02"})), each _>0 )))

Blessed are the knowledgeable! And the Helpful!
Thank you very much, you made my day! [and made a bit less stupid]