Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
beginner
Helper I
Helper I

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?

1 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:

BA_Pete_0-1677600556831.png

 

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

BA_Pete_1-1677600665616.png

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
artemus
Employee
Employee

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

LocationJan 23Feb 2323-Mar
New York012 
Los Angeles121222
Tokio000
San Antonio000
…..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:

BA_Pete_0-1677600556831.png

 

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

BA_Pete_1-1677600665616.png

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors