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