Forum Discussion
Solved
Matrix Values from Different Columns in Data Source?
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?
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"
3 Replies
That's a standard "Unpivot in groups" pattern. What have you tried and where are you stuck?
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
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"
