Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I have below data. I want to create a table visual as shown further. I want to show only latest three year data in the power bi table. To add he data in table, I had to unpivot the original table but then it makes it difficult to get only latest three years data in the table. Can u pls help me to create a power bi table which will look same as shown at the bottom & will keep changing the years in the colum header for latest three years.
Original table-
Student name | Year | Subject1 | Subject2 | Subject3 | Subject4 | Subject5 | Subject6 |
A1 | 2015 | 5 | 6 | 5 | 7 | 3 | 1 |
A1 | 2016 | 7 | 6 | 5 | 5 | 2 | 6 |
A1 | 2017 | 1 | 8 | 3 | 5 | 9 | 7 |
A1 | 2018 | 5 | 9 | 2 | 3 | 1 | 7 |
A1 | 2019 | 3 | 7 | 9 | 5 | 3 | 6 |
A1 | 2020 | 2 | 1 | 3 | 1 | 7 | 5 |
A2 | 2015 | 2 | 9 | 3 | 4 | 8 | 9 |
A2 | 2016 | 3 | 6 | 9 | 1 | 6 | 4 |
A2 | 2017 | 3 | 1 | 6 | 8 | 4 | 2 |
A2 | 2018 | 3 | 5 | 1 | 1 | 6 | 8 |
A2 | 2019 | 4 | 5 | 9 | 2 | 7 | 4 |
A2 | 2020 | 1 | 5 | 3 | 2 | 7 | 8 |
A3 | 2015 | 6 | 8 | 4 | 6 | 5 | 6 |
A3 | 2016 | 5 | 4 | 7 | 9 | 8 | 6 |
A3 | 2017 | 7 | 6 | 7 | 2 | 6 | 3 |
A3 | 2018 | 3 | 8 | 6 | 4 | 9 | 3 |
A3 | 2019 | 9 | 2 | 7 | 9 | 5 | 5 |
A3 | 2020 | 7 | 6 | 1 | 7 | 2 | 6 |
Expected Power bi Table with Student name slicer-
(Selected Student A1 in slicer)
Subject | 2020 | 2019 | 2018 |
Subject1 | 2 | 3 | 5 |
Subject2 | 1 | 7 | 9 |
Subject3 | 3 | 9 | 2 |
Subject4 | 1 | 5 | 3 |
Subject5 | 7 | 3 | 1 |
Subject6 | 5 | 6 | 7 |
Solved! Go to Solution.
Hey @harshadrokade
you can this code snippet in Power Query
//the latest year will be determined
maxyear = List.Max( #"Changed Type"[Year] ),
// the latest year will be used to filter the rows ... > maxyear -2
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Year] >= maxyear - 2),
//all the other columns will be unpivoted Subject1 ... Subjectn
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Student name", "Year"}, "Attribute", "Value")
This will provide a table that looks like this:
Hopefully, this provides what you are looking for.
Regards,
Tom
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! |
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! |
Hey @harshadrokade
you can this code snippet in Power Query
//the latest year will be determined
maxyear = List.Max( #"Changed Type"[Year] ),
// the latest year will be used to filter the rows ... > maxyear -2
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Year] >= maxyear - 2),
//all the other columns will be unpivoted Subject1 ... Subjectn
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Student name", "Year"}, "Attribute", "Value")
This will provide a table that looks like this:
Hopefully, this provides what you are looking for.
Regards,
Tom
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
90 | |
80 | |
60 | |
59 | |
58 |
User | Count |
---|---|
157 | |
118 | |
101 | |
76 | |
68 |