Mass shooting data

For this month #LondonPUGDataViz, we are 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