I have following data (simplified):

I have following data (simplified):

I need to calculate average value for EACH column (dynamic number) that has pair (VAL_A -> VAL_A_x) and REPLACE value in its mother column (VAL_A). Something VAL_A = (VAL_A + VAL_A_x)/2. Then I will delete columns ending with _x (It is simple). In the end, this is needed to achieve:

I have a lot of columns without a pair to average with. I have a lot of rows. I don't like to transpose the whole table. Columns are decimal numbers.

I tried to filter columns that are ending with "_x"

= List.Select(Table.ColumnNames(Source), each Text.EndsWith(_,"_x")),

but not getting far from that.

• excel
• powerquery
• m
• dynamic-tables

• Hi Stefan! I wonder how did you get to the 2.5. Following the steps you described, Val_A average for the column is 3. So you would have Val_A_x =3. So then for row 1, Val_a+Val_a_x divided by 2 is 2. And for row2 is 4, instead of 7. Is there more information you could share? – Optimistic Busy Commented Apr 1 at 20:04
• I believe 2.1.2025 [VAL_A] should be 6.5 , shouldn't it? – Ron Rosenfeld Commented Apr 1 at 20:42
• Maybe You or Me didn't catch parentheses correctly: Average of (VAL_A and VAL_A_x) on the row. | First row: (1+4)/2=2.5 | | Second: (5+8)/2=6.5 | (my mistake) – Stefan Billik Commented Apr 2 at 6:06

3 Answers 3

Try this:

Custom Function
Be sure to rename this as per the comments

//rename fnAverageColumns

(tbl as table, col as text)=>
[a=List.Select(Table.ColumnNames(tbl), each  _ = "DATETIME" or Text.StartsWith(_,col)),
 b=Table.SelectColumns(tbl,a),
 c=Table.AddColumn(b,"Average", each List.Average(List.Skip(Record.FieldValues(_))), type number),
 d=Table.ReplaceValue(
        c,
        each Record.Field(_,col),
        each [Average],
        (x,y,z) as number=>z,
        {col}),
 e=Table.SelectColumns(d,{"DATETIME",col})
 ][e]

Main Query

let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    
    #"Changed Type" = Table.TransformColumnTypes(Source,
        {{"DATETIME", type date}} & List.Transform(List.Skip(Table.ColumnNames(Source)), each {_, type number})),
    #"Averages" = List.Accumulate(
        List.Select(List.Skip(Table.ColumnNames(#"Changed Type")), each not Text.EndsWith(_,"_x")),
        Table.SelectColumns(#"Changed Type",{"DATETIME"}),
        (s,c)=> Table.Join(s,"DATETIME", fnAverageColumns(#"Changed Type",c), "DATETIME"))
in
    #"Averages"

Data

Results

One solution is simple, second one is interesting with List.Accumulate()
I'll have to try both and choose fastest solution.
In the night I decided to replace *_x values with average of * and *_x:

#"DATA" = #"Changed Type"
#"AVERAGED" = Table.FromRecords(Table.TransformRows(#"DATA", each _ & Record.FromList( List.Transform(List.Select(Record.FieldNames(_), each Text.EndsWith(_, "_x")),
    (name) => Record.Field(_, name)*0.5 + Record.Field(_, Text.BeforeDelimiter(name, "_x"))*0.5 ), List.Select(Record.FieldNames(_), each Text.EndsWith(_, "_x")))), Value.Type(#"DATA")),

It's not so nice.

The easiest would be to "transpose" or unpivot columns, then to pivot columns back again after doing a Group By with an extract text on the second occurrence of the delimiter.

Example:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"DATETIME"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Column", each Text.BeforeDelimiter([Attribute], "_", 1)),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"DATETIME", "Column"}, {{"Value", each List.Average([Value]), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Column]), "Column", "Value", List.Sum)
in
   #"Pivoted Column"