Microsoft Power-BI
This documentation helps you to integrate Wordbee API With Power-BI
How to create custom requests on PowerBi Desktop
Step 1
Open PowerBI Desktop:
Step 2
Create a new blank query, go to the header menu and select "New Source > Empty Query"
Step 3
"Edit Queries" in order to create new specific queries for Wordbee API:
Request samples:
Change the query like the next part.
Reference: https://docs.microsoft.com/en-us/power-bi/desktop-query-overview
API v1
Get projects
let /***************************** * Variables *****************************/ url = "http://api.wordbee-translator.com:32490/api/connect?account=accountCode&pwd=XXXXXXX", urlProjectsList = "http://api.wordbee-translator.com:32490/projects?token=", GetXml = Web.Contents(url), XmlToken = Xml.Tables(GetXml), AccessToken = XmlToken{0}[#"Element:Text"], /***************************** * Results *****************************/ Source = Xml.Tables(Web.Contents(Text.Combine({urlProjectsList, AccessToken}))), _Table = Source{0}[Table], Table = Table.TransformColumnTypes(_Table,{{"ClientCompanyId", Int64.Type}, {"ClientName", type text}, {"CreationDate", type datetime}, {"DefaultRefMaterialAccess", Int64.Type}, {"InProgressDate", type datetime}, {"OnlineUrl", type text}, {"ProjectId", Int64.Type}, {"ProjectType", type text}, {"ReceivedDate", type datetime}, {"Reference", type text}, {"ResourceId", Int64.Type}, {"SourceLocale", type text}, {"Status_", Int64.Type}}) in Table
API v2
Get persons labels
let /***************************** * Variables *****************************/ url = "http://www.wordbee-translator.com/api/auth/token", urlPersonLabels = "http://www.wordbee-translator.com/api/persons/labels", /***************************** * Functions *****************************/ GetJson = Web.Contents(url, [ Headers = [#"Accept"="application/json", #"Content-Type"="application/json;charset=UTF-8"], Content = Text.ToBinary("{'accountid': 'accountCode', 'key': 'XXXX-XXXX-XXXX-XXXX-XXXXXXXX'}") ]), /***************************** * Results *****************************/ FormatAsJson = Json.Document(GetJson), AccessToken = FormatAsJson, Source = Json.Document(Web.Contents(urlPersonLabels, [Headers=[#"Accept"="application/json", #"Content-Type"="application/json;charset=UTF-8", #"X-Auth-AccountId"="accountCode", #"X-Auth-Token"= AccessToken ]])) in Source
Get Label and expands options for multiple lines
let /***************************** * Variables *****************************/ url = "http://www.wordbee-translator.com/api/auth/token", urlPersonLabels = "http://www.wordbee-translator.com/api/persons/labels", /***************************** * Functions *****************************/ GetJson = Web.Contents(url, [Headers = [#"Accept"="application/json", #"Content-Type"="application/json;charset=UTF-8"], Content = Text.ToBinary("{'accountid': 'accountCode', 'key': 'XXXX-XXX-XXXX-XXX-XXXXXXXX'}") ]), FormatAsJson = Json.Document(GetJson), AccessToken = FormatAsJson, /***************************** * Results *****************************/ Source = Json.Document(Web.Contents(urlPersonLabels, [Headers=[ #"Accept"="application/json", #"Content-Type"="application/json;charset=UTF-8", #"X-Auth-AccountId"="accountCode", #"X-Auth-Token"= AccessToken ]])), labels = Source[labels], #"Converti en table" = Table.FromList(labels, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Column1 développé" = Table.ExpandRecordColumn(#"Converti en table", "Column1", {"id", "name", "isSticky", "isStickyInLists", "options"}, {"Column1.id", "Column1.name", "Column1.isSticky", "Column1.isStickyInLists", "Column1.options"}), #"Column1.options développé" = Table.ExpandListColumn(#"Column1 développé", "Column1.options"), #"Column1.options développé1" = Table.ExpandRecordColumn(#"Column1.options développé", "Column1.options", {"name", "value", "htmlColor"}, {"Column1.options.name", "Column1.options.value", "Column1.options.htmlColor"}) in #"Column1.options développé1"
Get all jobs with pagiation
let /***************************** * Variables *****************************/ url = "https://www.wordbee-translator.com/api/auth/token", urlJobsList = "https://www.wordbee-translator.com/api/jobs/list", maxItems = 200, apiKey = "XXXX-XXXX-XXXX-XXXX-XXXX", accountid = "XXXX", /***************************** * Functions *****************************/ GetToken = Web.Contents(url, [Headers = [ #"Accept"="application/json", #"Content-Type"="application/json;charset=UTF-8"], Content = Text.ToBinary("{'accountid': '" & accountid &"', 'key': '" & apiKey & "'}")]), AccessToken = Json.Document(GetToken), GetBody = (page, take) => "{'query': '{created}.Matches(""-2m"", "">="")', 'take': '" & Text.From(take) & "','skip': '" & Text.From(page * take) & "'}", GetPartial = (page, take) => let body = GetBody(page, take), Value = Json.Document(Web.Contents(urlJobsList, [ Headers=[ #"Accept"="application/json", #"Content-Type"="application/json;charset=UTF-8", #"X-Auth-AccountId"= accountid, #"X-Auth-Token"= AccessToken ], Content = Text.ToBinary(body) ])) in Value, GetRange = (page) => GetPartial(page, maxItems), /***************************** * Results *****************************/ Total = GetPartial(0, 0)[total], PageCount = Number.RoundUp(Total / maxItems), PageIndices = { 0 .. PageCount - 1 }, Pages = List.Transform(PageIndices, each GetRange(_)[rows]), Entities = List.Union(Pages), Source = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in Source
Copyright Wordbee - Buzzin' Outside the Box since 2008