Forum Discussion
How to matrix visualization with range of row values
Hello, I have the following table in Excel. I want to create a matrix visualization in Power BI, with name as columns and week numbers as rows (week 1 to 52, individually). But the problem is that i...
tricky solution
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc09C8IwEAbgvxIyd/CDiq4WdOkgOIYMr/RaAmkSrt7irzeJHyAO7w0Pd/caoze60ducI3lPE3ggbRtTqeTE5NQF4mPlst3m9O4hrkr7EchI4SY8/bKEO5Yv7f+b1qtsdXRxwQwfkRDUQKoDJ/fqLXeHnCvREHmstnvbGSF/w5yEtbVP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Week Number" = _t, #"End Week Number" = _t, #"Student Name" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Week Number", Int64.Type}, {"End Week Number", Int64.Type}, {"Student Name", type text}}), Span = Table.CombineColumns(#"Changed Type", {"Start Week Number", "End Week Number"}, each let l={_{0}.._{1}} in Record.FromList(l, List.Transform(l, each "WK" & Number.ToText(_, "00"))), "Span"), #"Reordered Columns" = Table.ReorderColumns(Span,{"Student Name", "Span"}), #"Expanded Span" = Table.ExpandRecordColumn(#"Reordered Columns", "Span", List.Transform({1..52}, each "WK" & Number.ToText(_, "00"))) in #"Expanded Span"
CNENFRNL
Community Champion
Community Championtricky solution
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc09C8IwEAbgvxIyd/CDiq4WdOkgOIYMr/RaAmkSrt7irzeJHyAO7w0Pd/caoze60ducI3lPE3ggbRtTqeTE5NQF4mPlst3m9O4hrkr7EchI4SY8/bKEO5Yv7f+b1qtsdXRxwQwfkRDUQKoDJ/fqLXeHnCvREHmstnvbGSF/w5yEtbVP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Week Number" = _t, #"End Week Number" = _t, #"Student Name" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Week Number", Int64.Type}, {"End Week Number", Int64.Type}, {"Student Name", type text}}),
Span = Table.CombineColumns(#"Changed Type", {"Start Week Number", "End Week Number"}, each let l={_{0}.._{1}} in Record.FromList(l, List.Transform(l, each "WK" & Number.ToText(_, "00"))), "Span"),
#"Reordered Columns" = Table.ReorderColumns(Span,{"Student Name", "Span"}),
#"Expanded Span" = Table.ExpandRecordColumn(#"Reordered Columns", "Span", List.Transform({1..52}, each "WK" & Number.ToText(_, "00")))
in
#"Expanded Span"