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
CODE
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
TableAPI v2
Get persons labels
CODE
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
SourceGet Label and expands options for multiple lines
CODE
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
CODE
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