How to populate missing location

[beginner]

My dataset is count incident days based on location for each month. I have other table where I keep locations(with different abbreviations). Some of the locations don't have any incidents and it does not show up in my graph. Is there way to populate location that don't have any incidents and populate each month with "0" so it will show "0" in the graph?

[artemus]

Just right click the cell you want to change, and in the menu choose replace values.... This will replace all of those values in that column with the value you choose.

[beginner]

Incident Table

Location	Jan 23	Feb 23	23-Mar
New York	0	12
Los Angeles	12	12	22
Tokio	0	0	0
San Antonio	0	0	0
…..goes on

Location Table

Location Name

New York

Dallas

New Jersey

Tokio

San Antonio

............. goes on

I am not sure if I understood it. My Incident table is joined with Location table based on Location name. However, since there are no incidents for Dallas it does not show up in my report. When there is an incident it is fine but without a incident my line graph is empty 

[BA_Pete · Accepted solution]

Hi @beginner ,

You should first select the [Location] column in your Incidents table in Power Query and go to the Transform tab > Unpivot Columns (dropdown) > Unpivot Other Columns. This is the 'correct' structure for any data to be used in Power BI:

To save model space and optimise performance, you should also filter out any zero values, so you have this:

Once applied to the data model, write two measures, like this:

_noofIncidents = SUM(IncidentsTable[Value])

_noofIncidents0 = SUM(IncidentsTable[Value]) + 0

Now, if you want to visualise only where incidents have happened, you can use LocationTable[Location] and [_noofIncidents] in your visual.

If you want to visualise all locations, you can use LocationTable[Location] and [_noofIncidents0] in your visual.

Pete

[artemus]

In that case, you would take your location table, do a merge (left join) with your incident table. Then expand the incident table (don't include the incidente's table location column). This will result in a single row with null values for any location not matched. You can then replace this with 0.