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...
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"
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"