Microsoft Power-BI

Owned by Stephan Böhmig
Last updated: Nov 15, 2022 by Brahim Aïoun

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