Mass shooting data

For this month #LondonPUGDataViz, we’re going to play with Gun violence archive data. This data is very interesting because it can tell a powerful story.

You can find the dataset here – Http://www.gunviolencearchive.org/reports/mass-shooting?year=2018

Data source URL is nicely formatted, you can use year as a parameter and get data for that year. Here is a sample file I created with the Power query in it for a quick start. You don’t have to use this model or this particular dataset. Our topic is Gun Violence and our tool is Power BI 😊

Looking forward to seeing your submissions 😊

URL for sample Model – Https://1drv.ms/f/s! Avm7gbgZtlMlwE39PrTxLVlIBPxK

Power Query used for sample model:

Parameters:

StartYear

2017 meta [IsParameterQuery=true, Type=“Any”, IsParameterQueryRequired=true]

EndYear

2018 meta [IsParameterQuery=true, Type=“Any”, IsParameterQueryRequired=true]

Function****GetData

(PageNumber as text,
Year as text) = > let URL = "http:
//www.gunviolencearchive.org/reports/mass-shooting?page="&PageNumber&"&year="&Year,
Source = Web.Page(Web.Contents(URL)),
Data0 = Source{0}[Data],
#"Removed Columns" = Table.RemoveColumns(Data0,{"Operations"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Incident Date",
type date}, {"State",
type text}, {"City Or County",
type text}, {"Address",
type text}, {"# Killed",
Int64.Type}, {"# Injured",
Int64.Type}})
in
    #"Changed Type"

Query****Data

let
    Source = {StartYear..EndYear},
    #"Converted to Table" = Table.FromList(Source,
    Splitter.SplitByNothing(),
    null,
    null,
    ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "PageNumber",
    each {1..18}),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Column1", "Year"}}),
    #"Expanded PageNumber" = Table.ExpandListColumn(#"Renamed Columns", "PageNumber"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded PageNumber",{{"Year",
    type text}, {"PageNumber",
    type text}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "GetData",
    each GetData([PageNumber], [Year])),
    #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"Year", "PageNumber"}),
    #"Expanded GetData" = Table.ExpandTableColumn(#"Removed Columns", "GetData", {"Incident Date", "State", "City Or County", "Address", "# Killed", "# Injured"}, {"Incident Date", "State", "City Or County", "Address", "# Killed", "# Injured"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded GetData"),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Incident Date",
    Order.Ascending}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Sorted Rows",{{"Incident Date",
    type date}, {"State",
    type text}, {"City Or County",
    type text}, {"Address",
    type text}, {"# Killed",
    Int64.Type}, {"# Injured",
    Int64.Type}})in #"Changed Type1"

Previous submissions – Https://prathy.com/category/londonpugdataviz/

London Power BI Meetup – Https://www.meetup.com/London-PUG/

Prathy 🙂

P. S. I showed a different data set during the event, but I just realised it was displaying limited data set. Hence changed the data set to mass shooting than all incidents