Hurricane Harvey – Help for Victims on Facebook – As Seen in #PowerBI

As the impact of Hurricane Harvey unfolds, and the worst may yet to come, I was wondering if data can help in the rescue efforts, or at least encourage us somehow. While the typical reports are quite pessimistic, and focus on Houston flooding maps (e.g. here), perhaps social media data will be more optimistic.

Since Harvey started, Social Media was used to call for help, and direct the rescue efforts to those in dire need. I thought it will be encouraging to show some data on those relevant Facebook pages (here is the data on Facebook), and use Power BI for the analysis of locations, and sentiment analysis of page posts and fans’ comments. Some of the pages in this report offer help, or connect between victims and support organizations. Other pages are just for fun, or opportunistically try to gain new audience for their page.

You can navigate to the second page and find the most positive and negative posts and comments.

Do you have ideas how to use this data, or mash it up with other sources in a useful way?

Here are the queries I used to create the report:

let
    iterations = Iterations,
    keyword = "Hurricane Harvey",
    url = "https://graph.facebook.com/v2.7/" &
        "search?type=Page&q=" & keyword &
        "&fields=name,location,picture,fan_count,about,category,posts{message,created_time,shares,comments.order(reverse_chronological){message}}" &
        "&access_token=" & MyAccessToken,
    
    
    generatedList = List.Generate(
        ()=>[i=0, res = FnGetSingleSearch(url)],
        each [i]<iterations and [res][Data]<>null,
        each [i=[i]+1, res = FnGetSingleSearch([res][Next])],
        each [res][Data]),
    #"Converted to Table" = Table.FromList(generatedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"name", "picture", "fan_count", "about", "category", "posts", "id", "location"}, {"name", "picture", "fan_count", "about", "category", "posts", "id", "location"}),
    #"Expanded location" = Table.ExpandRecordColumn(#"Expanded Column2", "location", {"street", "city", "state", "country", "zip", "latitude", "longitude"}, {"location.street", "location.city", "location.state", "location.country", "location.zip", "location.latitude", "location.longitude"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded location",{{"fan_count", Int64.Type}})
in
    #"Changed Type"

MyAccessToken should be replaced with your Facebook Access Token. You can obtain it manually here.

Here is the FnGetSingleSearch function:

(url) =>
let
    Source = Json.Document(Web.Contents(url)),
    data = try Source[data] otherwise null,
    next = try Source[paging][next] otherwise null,

    res = [Data=data, Next=next]
in
    res

You can download a static version of the .pbix report file here.

Crossing fingers for the safety of our friends at Texas.

Give Kudos for this report on Power BI Data Stories Gallery.