Forum Discussion
How to populate missing location
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
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
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