Displaying Instances of Data in PowerBI Report that isn't in Database?

[hyz7cougar]

Hello, 

I'm trying to create a simple PowerBI report from data that is pulled from a SharePoint list. I was wondering if there is a way to create a table that allows me to display data for a category that isn't in the field? In other words, let's say I have a simple sharepoint list that has two fields: the person's name, and the person's favorite alphabet letter. If no one in the list inputs the letter 'B', I would like the report to show me in a count of all favorite alphabet letter's in the database, and show me that no one input the letter 'B'. 

See illustration below as an example:

SharePoint List Data:

Name	Favorite Letter
Jack	A
Jerry	A
John	C

What I'd like to see in a table on a PowerBI Report:

Favorite Letter	Count of Favorite Letter
A	2
B	0
C	1

Does anyone know how I can achieve this?

[pranit828 · Accepted solution]

Hi @hyz7cougar 

You would need to create a new table with all the alphabets you would like the count for if they are not in the first table. Basically, This would be a master table with all the values needed in output count.
Join this new table with your sharepoint list data on favourite list column and while plotting a chart use the column from the new table.