Substitute columns with a single column of row average

[dejudicibus]

I have a table where the first column contains a label and the next N columns an integer value. For example (N=5):

A	7	2	6	5	4
B	3	5	1	4	3
C	1	4	5	7	3
D	5	1	7	1	7
E	3	5	2	3	1
F	7	1	5	4	6
G	4	6	4	5	2

I want to substitute all column from 2 to N with the average value of each row. For example 

A	4,8
B	3,2
C	4,0
D	4,2
E	2,8
F	4,6
G	4,2

How can I do that in the Power Query Editor?

[dejudicibus]

 complicated... I got through with POwerBI to move to Python to obtain what I need

[dejudicibus]

Well, it looks like a solution that is strongly linked to a specific table (5 columns). I need a more generic solution that simply creates a column filled by the average of values in rows.

[smpa01]

@dejudicibus  I differ with your comment, it is a very generic solution. 

Replace the first step with the following

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY65DQAhDAR7cUzCH9/L9YDov41bW0baYAR4vCvmlEOCdJBAAxUUEGWFKSdu2afRTbZttRdNq/dkQ+1NuU5nMftQc/J7tLfalzL7R81QO2hSqKOb/Ty3W/cGsusH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CAT = _t, n1 = _t, n2 = _t, n3 = _t, n4 = _t, n5 = _t, n6 = _t]),

 and see how the rest of the steps dynamically evaluate to give you a result set.

So the new Source will be this

| CAT | n1 | n2 | n3 | n4 | n5 | n6 |
|-----|----|----|----|----|----|----|
| A   | 7  | 2  | 6  | 5  | 4  | 1  |
| B   | 3  | 5  | 1  | 4  | 3  | 2  |
| C   | 1  | 4  | 5  | 7  | 3  | 3  |
| D   | 5  | 1  | 7  | 1  | 7  | 4  |
| E   | 3  | 5  | 2  | 3  | 1  | 5  |
| F   | 7  | 1  | 5  | 4  | 6  | 6  |
| G   | 4  | 6  | 4  | 5  | 2  | 7  |
| H   | 1  | 2  | 3  | 4  | 5  | 6  |

and the evaluation will result in this without you needing to change anything at all.

| Column1 | Column2     | Column3 | Column4 | Column5     | Column6     | Column7     | Column8     | Column9 |
|---------|-------------|---------|---------|-------------|-------------|-------------|-------------|---------|
| A       | 4.166666667 | 3       | 4       | 4.166666667 | 3.166666667 | 4.833333333 | 4.666666667 | 3.5     |
| B       | 4.166666667 | 3       | 4       | 4.166666667 | 3.166666667 | 4.833333333 | 4.666666667 | 3.5     |
| C       | 4.166666667 | 3       | 4       | 4.166666667 | 3.166666667 | 4.833333333 | 4.666666667 | 3.5     |
| D       | 4.166666667 | 3       | 4       | 4.166666667 | 3.166666667 | 4.833333333 | 4.666666667 | 3.5     |
| E       | 4.166666667 | 3       | 4       | 4.166666667 | 3.166666667 | 4.833333333 | 4.666666667 | 3.5     |
| F       | 4.166666667 | 3       | 4       | 4.166666667 | 3.166666667 | 4.833333333 | 4.666666667 | 3.5     |
| G       | 4.166666667 | 3       | 4       | 4.166666667 | 3.166666667 | 4.833333333 | 4.666666667 | 3.5     |
| H       | 4.166666667 | 3       | 4       | 4.166666667 | 3.166666667 | 4.833333333 | 4.666666667 | 3.5     |

[dejudicibus]

Why 9 columns? I need only 2: Column1 for Labels and Column2 for average values...

For example, for

| CAT | n1 | n2 | n3 | n4 | n5 | n6 |
|-----|----|----|----|----|----|----|
| A   | 7  | 2  | 6  | 5  | 4  | 1  |
| B   | 3  | 5  | 1  | 4  | 3  | 2  |
| C   | 1  | 4  | 5  | 7  | 3  | 3  |
| D   | 5  | 1  | 7  | 1  | 7  | 4  |
| E   | 3  | 5  | 2  | 3  | 1  | 5  |
| F   | 7  | 1  | 5  | 4  | 6  | 6  |
| G   | 4  | 6  | 4  | 5  | 2  | 7  |
| H   | 1  | 2  | 3  | 4  | 5  | 6  |

it should generate

| CAT | a1 |
|-----|----|
| A   | 4.2  | 
| B   | 3.0  |
| C   | 3.8  |
| D   | 4.2  |
| E   | 3.2  |
| F   | 4.8  |
| G   | 4.7  |
| H   | 3.5  |

[smpa01 · Accepted solution]

@dejudicibus 

Why 9 columns? because you asked I want to substitute all column from 2 to N with the average value of each row

However, if you are happy with 2 columns, follow this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIHYiMgNgNiUyA2UYrViVZyArKMoSKGYFEQHyTjjCRiCtUPkXFBUm8Op0EyrkimGUHZhmAZNyS1plBTzcAy7nAewi4jpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CAT = _t, n1 = _t, n2 = _t, n3 = _t, n4 = _t, n5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CAT", type text}, {"n1", type text}, {"n2", type text}, {"n3", type text}, {"n4", type text}, {"n5", type text}}),
    Loop = List.Transform(List.Transform(Table.ToList(#"Changed Type"),each List.Average(List.Transform(List.RemoveFirstN(Text.Split(_,","),1),each Number.From(_)))),each Number.ToText(_)),
    #"Removed Other Columns" = Table.FromColumns(Table.ToColumns(Table.SelectColumns(#"Changed Type",{"CAT"}))&{Loop},List.Combine({Table.ColumnNames(Table.SelectColumns(#"Changed Type",{"CAT"})),{"Average"}})),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Average", type number}})
in
    #"Changed Type1"

[smpa01]

@dejudicibus  did you try this?

[smpa01]

@dejudicibus  you can try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIHYiMgNgNiUyA2UYrViVZyArKMoSKGYFEQHyTjjCRiCtUPkXFBUm8Op0EyrkimGUHZhmAZNyS1plBTzcAy7nAewi4jpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CAT = _t, n1 = _t, n2 = _t, n3 = _t, n4 = _t, n5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CAT", type text}, {"n1", type text}, {"n2", type text}, {"n3", type text}, {"n4", type text}, {"n5", type text}}),
    Custom1 = List.Transform(List.Transform(Table.ToList(#"Changed Type"),each List.Average(List.Transform(List.RemoveFirstN(Text.Split(_,","),1),each Number.From(_)))),each Number.ToText(_)),
    Base = Table.SelectColumns(Source,{"CAT"})[CAT],
    Loop = List.Generate(
                         ()=> [i=0,k=List.Transform(Base, each Text.Combine({_,Custom1{i}},","))],
                         each [i]<List.Count(Custom1),
                         each [i=[i]+1,k=List.Transform([k], each Text.Combine({_,Custom1{i}},","))],
                         each [k]
    ),
    Result = Loop{List.Count(Loop)-1},
    #"Converted to Table" = Table.FromList(Result, Splitter.SplitTextByDelimiter(","), null, null, ExtraValues.Error),
    #"Changed Type1" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}, {"Column2", type number}, {"Column3", type number}, {"Column4", Int64.Type}, {"Column5", type number}, {"Column6", type number}, {"Column7", type number}, {"Column8", type number}})
    in #"Changed Type1"

Pbix is attached