Integration / connection to WikiTree online API

[Anonymous]

Dear Community. I spent already 2 days trying to slove it but no success.

The best genealogy service Wikitree has API, so i am trying to connect to it with power BI but it doesnt work.

For example if i run this in browser:

https://apps.wikitree.com/api.php?action=getWatchlist

it retrieves Well-Formatted JSON in browser for logged in user. This is perfect thing!

But When i make same via connection in power bi, it seems it doesnt login at all.

It simply returns empty JSON. No data. Like for user which wasnt login.

Please asking to someone who has good knowledge - try to create account (if you dont have any) in wikitree and try to make Power Bi connection to API. I run out of ideas whats wrong.

ASking community in Wikitree didnt help - nobody tried this so far - to run data queries by power bi 🙂

Tank you in Advance, Fedir

[v-piga-msft]

Hi @Anonymous ,

Please have reference of this query about how to connect to API in Power BI Desktop.

Spoiler

/*
This M script gets an bearer token and performs a tweet search from the Twitter REST API
https://dev.twitter.com/oauth/application-only

Requires establishing a Twitter application in order to obtain a Consumer Key & Consumer Secret
https://apps.twitter.com/

IMPORTANT - The Consumer Key and Consumer secret should be treated as passwords and not distributed
*/

let
// Concatenates the Consumer Key & Consumer Secret and converts to base64
authKey = "Basic " & Binary.ToText(Text.ToBinary("<ConsumerKey>:<ConsumerSecret>"),0),
url = "https://api.twitter.com/oauth2/token",
// Uses the Twitter POST oauth2/token method to obtain a bearer token
GetJson = Web.Contents(url,
[
Headers = [#"Authorization"=authKey,
#"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
Content = Text.ToBinary("grant_type=client_credentials")
]
),
FormatAsJson = Json.Document(GetJson),
// Gets token from the Json response
AccessToken = FormatAsJson[access_token],
AccessTokenHeader = "bearer " & AccessToken,
// Uses the Twitter GET search/tweets method using the bearer token from the previous POST oauth2/token method
GetJsonQuery = Web.Contents("https://api.twitter.com/1.1/search/tweets.json?q=Allegient&count=100",
[
Headers = [#"Authorization"=AccessTokenHeader]
]
),
FormatAsJsonQuery = Json.Document(GetJsonQuery),
NavigateToStatuses = FormatAsJsonQuery[statuses],
TableFromList = Table.FromList(NavigateToStatuses, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandColumn = Table.ExpandRecordColumn(TableFromList, "Column1", {"metadata", "created_at", "id", "id_str", "text", "source", "truncated", "in_reply_to_status_id", "in_reply_to_status_id_str", "in_reply_to_user_id", "in_reply_to_user_id_str", "in_reply_to_screen_name", "user", "geo", "coordinates", "place", "contributors", "is_quote_status", "retweet_count", "favorite_count", "entities", "favorited", "retweeted", "lang", "possibly_sensitive", "quoted_status_id", "quoted_status_id_str", "quoted_status"}, {"Column1.metadata", "Column1.created_at", "Column1.id", "Column1.id_str", "Column1.text", "Column1.source", "Column1.truncated", "Column1.in_reply_to_status_id", "Column1.in_reply_to_status_id_str", "Column1.in_reply_to_user_id", "Column1.in_reply_to_user_id_str", "Column1.in_reply_to_screen_name", "Column1.user", "Column1.geo", "Column1.coordinates", "Column1.place", "Column1.contributors", "Column1.is_quote_status", "Column1.retweet_count", "Column1.favorite_count", "Column1.entities", "Column1.favorited", "Column1.retweeted", "Column1.lang", "Column1.possibly_sensitive", "Column1.quoted_status_id", "Column1.quoted_status_id_str", "Column1.quoted_status"})
in
ExpandColumn

If you still need help, please share some screenshot about your problem so that we could understand your scenario better and help further on it.

Best  Regards,

Cherry

[Anonymous]

Hi Cherry, Thanks for replying. I was trying to adapt example you mentioned, but it seems that Twitter API is so far different from Wikitree, that i cannot adjust code.

It works well in browser but has issues in power bi.

Asking for help.