Help with Data modelling

[dandamudisanjay]

Hi All, 

We have our data as follows. 

ID	Cricket	Football	Baseball	Hockey	Rugby	Motorsport	Basketball	Tennis	Golf
1	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE
2	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE
3	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE
4	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE	TRUE	FALSE	FALSE
5	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE
6	TRUE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	TRUE
7	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	FALSE

We are looking to create a visualisation which shows total ID's along with Sport Name and interest as TRUE only. so the output we are looking to get is something like follows but in a visualisation with common axes as total of ID's

Sport	Total
Cricket	2
Football	2
Baseball	1
Hockey	3
Rugby	0
MotorSport	1
Basketball	2
Tennis	3
Golf	2

Could anyone please help with this?

Will highly appreciate your help with this. 

Regards

Sanjay

[Anonymous · Accepted solution]

Hi @dandamudisanjay ,

Here what you want to achieve is to unpivot your dataset, to go from what you have to:

Customer ID / Sport

1 Cricket
1 Football
1 Baseball
1 Golf
2 Hockey
3 Tennis
....

You'll find here a .pbix with an example based on your use case.

The steps I took are the following

1. Unpivot and prepare your data in Power Query

2. Filter out rows for which the value is false

3. Create a measure that counts the number of players per sport

Step 1 : unpivoting the data

- you select the columns with names of sport

- and then Transform > Unpivot Columns > Unpivot Columns

The next steps in Power Query are:

- renaming Attribute to something more meaningful like Sport

- filtering out on the value column rows with FALSE

- removing the value column which is now useless as it only contains TRUE

Step 2 : creating your measure in Power BI

Here it's simple:

Count of Players = COUNTROWS( SportsData )

Step 3 : create your visual

Also, there are some great content which explain you more about the unpivot step in PowerQuery, I'll let you search the web/youtube.

I hope it helps. Does it?

Best,

Thomas

[dandamudisanjay]

Thanks a lot Thomas. Worked like a treat.

[amitchandak]

@dandamudisanjay , If better to unpivot this data

https://radacad.com/pivot-and-unpivot-with-power-bi,

Refer: https://www.youtube.com/watch?v=2HjkBtxSM0g
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/