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 do not know how to expand the start and end week numbers to show the complete range of week numbers by student name. For example, start week number 5 and end week number 8, but i need to have week numbers as 5,6,7,8 for that student name. The matrix visualization should then show those cells as highlighted. Any help is much appreciated!!
| Start Week Number | End Week Number | Student Name |
| 2 | 3 | Bellegarde |
| 3 | 3 | Frei Paulo |
| 2 | 5 | Lizui |
| 5 | 5 | Laufenburg |
| 5 | 5 | Luntas |
| 5 | 8 | Bellegarde |
| 10 | 10 | Cosamaloapan de Carpio |
| 8 | 9 | Seedorf |
| 6 | 9 | Gangarampur |
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"
1 Reply
CNENFRNLCommunity Champion
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"