Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.