Table with variable colum headers

[harshadrokade]

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

[TomMartens · Accepted 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

[CNENFRNL · Accepted solution]

[TomMartens · Accepted 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