Table to show the same column multiple times but with different filters

[CEllis]

Morning all,

I was hoping to see if anyone can give me some guidance, the desired outcome is to create a table from the source table but filter the subject.

It looks to be a pivot table up to the point I want to see all subjects going across the columns which is where I come unstuck.

I am not sure if this is even possible.

Any help greatly appreciated.

Source Table

Name	Gender	Type	Class	Previouse	Current	Subject
Name1	Female	No	Class1	-16	-62	Subject1
Name1	Female	No	Class2	-48	-47	Subject2
Name1	Female	No	Class3	-71	-50	Subject3
Name2	Male	Yes	Class1	3	15	Subject1
Name2	Male	Yes	Class2	3	-8	Subject2
Name2	Male	Yes	Class3	-65	8	Subject3
Name3	Female	No	Class1	36	64	Subject1
Name3	Female	No	Class2	-21	-21	Subject2
Name3	Female	No	Class3	13	68	Subject3

Desired output table

			Subject1	Subject2	Subject3
Name	Gender	Type	Class	Previouse	Current	Class	Previouse	Current	Class	Previouse	Current
Name1	Female	No	Class1	-16	-62	Class2	-48	-47	Class3	-71	-50
Name2	Male	Yes	Class1	3	15	Class2	3	-8	Class3	-65	8
Name3	Female	No	Class1	36	64	Class2	-21	-21	Class3	13	68

Many thanks

Chris

[bhanu_gautam · Accepted solution]

@CEllis , Try pivot using below steps

Load your data into Power BI.

Transform Data:

Go to the "Home" tab and click on "Transform Data" to open the Power Query Editor.
Pivot the Data:

In the Power Query Editor, select the "Subject" column.
Go to the "Transform" tab and click on "Pivot Column".
In the "Values Column" dropdown, select "Class", "Previouse", and "Current".
Click "OK".
Adjust the Table:

The pivot operation will create new columns for each combination of "Subject" and the selected values ("Class", "Previouse", "Current").
Rename the columns as needed to match your desired output format.
Close and Apply:

Once you have the table in the desired format, click "Close & Apply" to load the transformed data back into Power BI.

[bhanu_gautam · Accepted solution]

@CEllis , Try pivot using below steps

Load your data into Power BI.

Transform Data:

Go to the "Home" tab and click on "Transform Data" to open the Power Query Editor.
Pivot the Data:

In the Power Query Editor, select the "Subject" column.
Go to the "Transform" tab and click on "Pivot Column".
In the "Values Column" dropdown, select "Class", "Previouse", and "Current".
Click "OK".
Adjust the Table:

The pivot operation will create new columns for each combination of "Subject" and the selected values ("Class", "Previouse", "Current").
Rename the columns as needed to match your desired output format.
Close and Apply:

Once you have the table in the desired format, click "Close & Apply" to load the transformed data back into Power BI.

[CEllis · Accepted solution]

Many thanks @bhanu_gautam  got me on the right track.

If I select the columns then unpivot columns it gives the desired result I can work with.

Many thanks

Chris

[CEllis]

Hi @bhanu_gautam 

Thanks for the quick response.

I cant seem to select all 3 fields.