Add Column Count of people across two tables to get the count of people from a city

Question

I have two tables in Power Query City and People.

CITY

Id, Name
 1, Brisbane
 2, Sydney
 3, Melbourne

PEOPLE

Id, Name,  CityId
 1, Jay,   1
 2, Sam,   2
 3, Paul,  1
 4, Sarah, 3

I'd like to add a column to City that shows a count of how many people belong to that city.

Answer 1

Hi Anitha,

1. Merge both tables, using City Id and Id into a new table.

2. Then create a column, count =  COUNTA(People[Name])

3. Add this measure, ID into visualization, to show each city and number of people in each.

Hope this helps you.