Forum Discussion
How to populate missing location
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?
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]) + 0Now, 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
4 Replies
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.
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
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.
