Google Image API - how to handle returned data?

[MarkSL]

Hi,

I'm trying out using API calls for the first time using Google's Place Search API: https://developers.google.com/places/web-service/search.  I have successfully called the main Place Search API , which returns Googles Opening Hours, Rating Score and Photo Reference.  

The Photo Reference is required to call the next API, Photo Search which I hope will return an image which I can then display in my reports.  Googles documentation states "The response of a successful Place Photo request will be an image. The type of the image will depend upon the type of the originally submitted photo."

I have managed to call the Photo Seach API and the result looks like so:

If I right click on the text I get the following options:

Does anyone know what I need to do with it now to allow me to display the image in Power BI?

Thanks

Mark

[tonmcg · Accepted solution]

You can't. At least according to this Stack Overflow answer. The response the Google API returns is in Binary format, which doesn't support image files.

It's interesting however, that when I navigate my browser to a Google Places API endpoint, it redirects me to a googleusercontent url tht contains images. I can use this URL of course to show the images. But I don't know how to capture the redirect URL programmatically through Power BI.

[MarkSL]

Hi,

Thanks for the input and that is a shame.  I am completely news to APIs and was pleased to be able to call the Places API without too much trouble!

I've looked into getting a Bing / Azure API Key and try to call the Bing Image Search API v7 instead, but I can't seem to find the equivalent Web call, eg the same format as the Google call I used previously (HTML?).   Something like this:

https://maps.googleapis.com/maps/api/place/findplacefromtext/json?input=
Tower%20Of%20London&inputtype=textquery&fields=photos,formatted_address,name,opening_hours,rating&key=xxxxxxxxxxxxxxxxxxxxxxxxx

The Bing documentation has code to build an API in: C#, Java, Node.js, PHP, Python, Ruby.  

How can go I about finding the Web call which Power BI would accept?

Thanks

Mark

[tonmcg]

The Bing Image Search API v7 seems to have what you're looking for. To use it, you'll need an Azure Cognitive Services account, which you can learn how to do here.

I quickly put together a Power Query M code sample that searches the Bing Image Search API for "Tower of London":

let
    API_KEY = <place your Bing Image Search API key here>,
    q = "Tower of London",
    domain = "https://api.cognitive.microsoft.com",
    path = "/bing/v7.0/images/search",
    endpoint = Uri.Combine(domain, path),
    request = 
        Web.Contents(
            endpoint,
            [
                Query = [
                    q = q
                ],
                Headers = [
                    #"Ocp-Apim-Subscription-Key" = API_KEY
                ]
            ]
        ),
    response = Json.Document(request),
    value = response[value],
    tableOfValues = Table.FromList(value, Splitter.SplitByNothing(), {"values"}, null, ExtraValues.Error),
    ExpandedValues = Table.ExpandRecordColumn(tableOfValues, "values", Record.FieldNames(tableOfValues{0}[values])),
    DefinedDataTypes = Table.TransformColumnTypes(ExpandedValues,{{"webSearchUrl", type text}, {"name", type text}, {"thumbnailUrl", type text}, {"datePublished", type datetime}, {"isFamilyFriendly", type logical}, {"creativeCommons", type text}, {"contentUrl", type text}, {"hostPageUrl", type text}, {"contentSize", type text}, {"encodingFormat", type text}, {"hostPageDisplayUrl", type text}, {"width", Int64.Type}, {"height", Int64.Type}, {"hostPageFavIconUrl", type text}, {"thumbnail", type any}, {"imageInsightsToken", type text}, {"insightsMetadata", type any}, {"imageId", type text}, {"accentColor", type text}})
in
    DefinedDataTypes

It gives me this result:

Note: I used the Chiclet Slicer to show these images in Power BI.

[MarkSL]

Wow @tonmcg - I really appreciate your help with that!

I've been able to get an Azure Key, create a custom function with a parameter using your script and it works well when using our 'Tower Of London' example.  However if I call it with something more obscure, then I get an error.

For example using a search of "vw golf gti" returns the error:

Expression.Error: The column 'creativeCommons' of the table wasn't found.
Details:
creativeCommons

Any thoughts?  

[tonmcg]

It sounds like results from the Bing Image Search API don't all have the same columns. Delete the last DefinedDataTypes step. It tries to define a data type for the creativeCommons column, which doesn't exist in these results.

[MarkSL]

Hi @tonmcg 

Apologies, I can't see any column 'creativeCommons '?  This is new territory for me so please forgive me if I am missing something obvious!

Another question if you could help.  From  function will return table of results.  How do I go about linking multiple rows to the field I've passed as a parameter in the function?

Thanks again,

Mark

[MarkSL]

Hi @tonmcg 

Now understood.  I've deleted the last line from the script 'DefinedDataTypes ' and its now working.

Thanks for all your help!