How to Table.Group with all columns of the Source

[Anonymous]

Hello,

I would like to Summarize all my columns in order to pivot my table and have only one column per Item:

here is my initial Table:

Mapping.Column2	col1	col2	col3	col...
A	1	0	2	…
C	2	3	0	…
B	0	2	1	…
C	3	2	0	…
A	1	0	2	…

The problem is that the number of columns is different each month, so I can’t specify exactly wich column have to be summarized (And I must summarize all of them).

I already tried to code the function by using the list of names of column,

= Table.Group(Source,{"Mapping.Column2"},
{{ Text.From( each List.RemoveItems(Table.ColumnNames(Source) ,{"Mapping.Column2"})),
each List.Sum(each List.RemoveItems({Table.ColumnNames(Source) ,"Mapping.Column2"})),
type anynonnull}})

but have the Expression error:

Cannont convert Function type into Text type.

Détails :

    Value=Function

    Type=Type

Here is the Table want after Grouping and Pivoting:

Mapping.Column2	A	B	C
Col1	2	0	5
Col2	0	2	5
Col3	4	1	0
Col..	…	…	…

Many thanks for your help,

Chris

[Jimmy801 · Accepted solution]

Hello @Anonymous 

combine a Unpivot with an Pivot. Try out this solution

let
	Source = #table 
	(
		{"Mapping.Column2","col1","col2","col3"},
		{ {"A","1","0","2"}, {"C","2","3","0"}, {"B","0","2","1"}, {"C","3","2","0"}, {"A","1","0","2"} } 
	),
	ChangedType = Table.TransformColumnTypes
	(
		Source,
		{{"col1", Int64.Type}, {"col2", Int64.Type}, {"col3", Int64.Type}}
	),
	UnpivotOther = Table.UnpivotOtherColumns
	(
		ChangedType,
		{"Mapping.Column2"},
		"Attribute",
		"Value"
	),
	Pivot = Table.Pivot
	(
		UnpivotOther,
		List.Distinct
		(
			UnpivotOther[Mapping.Column2]
		),
		"Mapping.Column2",
		"Value",
		List.Sum
	)
 in 
	Pivot

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Thank You, works very well (y)