upload image

Using the WikiTree API as a Data Source for Power BI

Privacy Level: Open (White)
Date: [unknown] [unknown]
Location: [unknown]
Surname/tag: wt_apps
Profile manager: Jamie Nelson private message [send private message]
This page has been accessed 437 times.

Contents

Get Power BI

Retrieving Public Data

  1. Go to the "Home" tab.
  2. Click on "Get Data".
  3. Choose "Web".
  4. Enter the API URL of the data you want to access.

Logging In

You will need to use the login action of the WikiTree API in order to retrieve private data such as your watchlist.

You will need your WikiTree account email and password.

To do this:

  1. Go to the "Home" tab.
  2. Click on "Get Data".
  3. Choose "Web".
  4. Enter http://apps.wikitree.com/api.php?action=login&email=EMAIL&password=PASSWORD, where EMAIL is your email, and PASSWORD is your password.
  5. If your login is successful, you should receive a userid, username, and token. You will need this for retrieving private data.

Retrieving Private Data

  1. Go to the "Home" tab.
  2. Click on "Get Data".
  3. Choose "Web".
  4. Choose "Advanced".
  5. In "URL parts", enter the API URL of the data you want to retrieve. For example, https://apps.wikitree.com/api.php?action=getWatchlist.
  6. Under "HTTP request header parameters", choose "Cookie".
  7. In the box next to "Cookie", you will need to enter information in the format wikidb_wtb_UserID=123; wikidb_wtb_UserName=Name-1; wikidb_wtb_Token=xyz123 using the information that you retrieved above:
    • wikidb_wtb_UserID is the userid.
    • wikidb_wtb_UserName is the username.
    • wikidb_wtb_Token is the token.

Tutorial (Example) how to connect in Power Bi and transform Data

Here is Script From Advanced Editor, how to make easy read from WatchList:

  1. let
  2. //Login to Wikitree
  3. SourceUserLogin = Json.Document(Web.Contents("http://apps.wikitree.com/api.php?action=login&email="& emailLogin &"&password=" & pass)),
  4. login = SourceUserLogin[login],
  5. #"Converted to Table" = Record.ToTable(login),
  6. // Get Token after login
  7. #"Filtered Rows token" = Table.SelectRows(#"Converted to Table", each [Name] = "token"),
  8. TokenValue = #"Filtered Rows token"{0}[Value],
  9. // Get Username after login
  10. #"Filtered Rows username" = Table.SelectRows(#"Converted to Table", each [Name] = "username"),
  11. UsernameValue = #"Filtered Rows username"{0}[Value],
  12. // Get Userid after login
  13. #"Filtered Rows userid" = Table.SelectRows(#"Converted to Table", each [Name] = "userid"),
  14. UseridValue = #"Filtered Rows userid"{0}[Value],
  15. Source = Json.Document(Web.Contents("https://apps.wikitree.com/api.php?action=getWatchlist",
  16. [Headers=[Cookie="wikidb_wtb_UserID=" & Number.ToText(UseridValue) & "; wikidb_wtb_UserName=" & UsernameValue & "; wikidb_wtb_Token=" & TokenValue ]])),
  17. Source1 = Source{0},
  18. watchlist = Source1[watchlist],
  19. #"Converted to Table11" = Table.FromList(watchlist, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  20. #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table11", "Column1", {"Id", "Name", "FirstName", "MiddleName", "LastNameAtBirth", "LastNameCurrent", "Nicknames", "LastNameOther", "RealName", "Prefix", "Suffix", "BirthLocation", "DeathLocation", "Gender", "BirthDate", "DeathDate", "BirthDateDecade", "DeathDateDecade", "Photo", "IsLiving", "HasChildren", "NoChildren", "Privacy", "IsPerson", "Touched", "ShortName", "BirthNamePrivate", "LongNamePrivate", "LongName", "BirthName", "Manager", "DataStatus", "Privacy_IsPrivate", "Privacy_IsPublic", "Privacy_IsOpen", "Privacy_IsAtLeastPublic", "Privacy_IsSemiPrivate", "Privacy_IsSemiPrivateBio", "PhotoData", "Father", "Mother", "Parents"}, {"Id", "Name", "FirstName", "MiddleName", "LastNameAtBirth", "LastNameCurrent", "Nicknames", "LastNameOther", "RealName", "Prefix", "Suffix", "BirthLocation", "DeathLocation", "Gender", "BirthDate", "DeathDate", "BirthDateDecade", "DeathDateDecade", "Photo", "IsLiving", "HasChildren", "NoChildren", "Privacy", "IsPerson", "Touched", "ShortName", "BirthNamePrivate", "LongNamePrivate", "LongName", "BirthName", "Manager", "DataStatus", "Privacy_IsPrivate", "Privacy_IsPublic", "Privacy_IsOpen", "Privacy_IsAtLeastPublic", "Privacy_IsSemiPrivate", "Privacy_IsSemiPrivateBio", "PhotoData", "Father", "Mother", "Parents"})
  21. in
  22. #"Expanded Column1"




Collaboration
  • Login to edit this profile and add images.
  • Private Messages: Send a private message to the Profile Manager. (Best when privacy is an issue.)
  • Public Comments: Login to post. (Best for messages specifically directed to those editing this profile. Limit 20 per day.)


Comments: 2

Leave a message for others who see this profile.
There are no comments yet.
Login to post a comment.
Thank you Jamie for finding a way to connect Power BI to Wikitree! I hope Community will find it valuable! I already was able to read Watchlist successfully. If anyone needs a PowerBI template feel free to ask.
posted by Fedir Indutnyi
Jamie,

Thank you very much for creating this page!

posted by Margreet Beers