Matrix Values from Different Columns in Data Source?

[Gerbil]

Let's say I have a data source that looks like this:

ID	A	B	C	D	E	F	G	H	I
1	a	b	c	d	e	f	g	h	i
2	a	b	c	d	e	f	g	h	i
3	a	b	c	d	e	f	g	h	i
4	a	b	c	d	e	f	g	h	i
5	a	b	c	d	e	f	g	h	i

I'd like to make a table like so:

	Col1	Col2	Col3
Row1	a	b	c
Row2	d	e	f
Row3	g	h	i
	sum(col1)	sum(col2)	sum(col3)

Where I can put in a custom column header and row header, as well as get totals of each column. This table will be filtered with a slicer to a specific ID, so it will display all the values associated with that ID.

Is this possible?

[lbendlin]

That's a standard "Unpivot in groups"  pattern.  What have you tried and where are you stuck?

[Gerbil]

I looked into unpivoting and it looks like a solution I can use. But can I change the query so that the table looks like this:

ID	Category	Row	Value
1	A	1	a
1	B	1	b
1	C	1	c
1	D	2	d
1	E	2	e
1	F	2	f
1	G	3	g
1	H	3	h
1	I	3	i

i.e. have two clauses for the category

[lbendlin · Accepted solution]

yes, that can be done as well.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoE4iQgTgbiFCBOBeI0IE4H4gwgzlSK1YlWMiJapTHRKk2IVmlKnMpYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, A = _t, B = _t, C = _t, D = _t, E = _t, F = _t, G = _t, H = _t, I = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID"}, "Category", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Row", 1, 1, Int64.Type),
    #"Replaced Value" = Table.ReplaceValue(#"Added Index",each [Row],each let n=Int64.From(Number.Mod([Row]+1,9)/3) in if n=0 then 3 else n,Replacer.ReplaceValue,{"Row"})
in
    #"Replaced Value"