Forum Discussion

THEG72's avatar
THEG72
Helper V
8 years ago

Refreshing A Desktop API in the service? Is this Possible?

I am accessing some accounts data via a software vendors desktop API package which resides on a terminal server.

With the accounts data The desktop API software allows me to query the accounting data which returns a json, xml or html data via querying the browser using the localhost command.

 

The normal API call restrictions dont apply however you do need to paginate to get all records. It does request a user name and password when accessing the data the first time in Power BI however after that it doesnt request it any more?

 

My sample code in my PBIX file installed on the terminal server is as follows:

 

let
    Source = Xml.Tables(Web.Contents("http://localhost:8080/Accounts/?api-version=v2&format=xml")),
    TableExpand = Table.ExpandTableColumn(Source, "Table", {"Id", "LibraryPath", "Name", "Uri"}, {"Id", "LibraryPath", "Name.1", "Uri"}),
    TableSelect = Table.SelectColumns(TableExpand,{"Id", "LibraryPath", "Name.1", "Uri"}),
    DataType = Table.TransformColumnTypes(TableSelect,{{"Id", type text}, {"Name.1", type text}, {"LibraryPath", type text}, {"Uri", type text}}),
    TableRename = Table.RenameColumns(DataType,{{"Name.1", "Entity Name"}, {"LibraryPath", "Library Path"}, {"Uri", "Company Front Door on This Device"}, {"Id", "CFUID"}})
in
    TableRename

Is it possible to refresh the contents of this PBIX file if i publish the report data in the service?

 

What kind of gate way is required and how do i setup this part on the service under the management gateways section?

 

I currently have a Power BI gateway installed in enterprise mode and working with Excel Data which is being updated correctly.

 

Or is the Power Update software option the only way to refresh this PBIX file which has a DESKTOP based API data source?


Thanks for any Help!

 

Need Help
Tutorial Requests

8 Replies

  • GilbertQ's avatar
    GilbertQ
    Super User
    8 years ago
    Hi there

    I would think that it should work via the Gateway, you possibly would have to set it up as a source in the Gateway in Power BI Service. And when setting it up you can put in the Username and password that it should use.

    Then when it gets refreshed it should then access it via the Gateway.

    I have not tested this before so not 100% sure if it will work.
    • THEG72's avatar
      THEG72
      Helper V
      8 years ago

      GilbertQ

       

      Thanks Gilbert, what should i fill out with gate way setup i tried this below which say it connects but i get dataset not supported in refresh section?

       

      Gateway connection works

      What URL should i Use ? The actual full URL to get the data is as follows for the contacts ledgers..

       

      http://localhost:8080/AccountRight/91575b41-0305-4c64-b319-b160ee9765a4/Contact/?api-version=v2&$top=500

       

      Is this a relative path issue explain by Chris Webb? How should i construct my URL then? As i need pagination some times by baseurl is as follows;

       

      BaseUrl = DataLocation & CFUID1 & "/Contact/?api-version=v2&$top=500"

       

      I have a parametr for Data location : http://localhost:8080/AccountRight/

       

      I have a parameter fro the UID name CFUID1 which returns the company filed id.

       

    • THEG72's avatar
      THEG72
      Helper V
      8 years ago

      GilbertQ

      Further to this Gilbert i got the refresh to work with a basic query

       

      The syntax i got to work for the single call is as follows

       

      let
       Source = Json.Document(Web.Contents("http://localhost:8080/Accounting/",
       [RelativePath="91575b41-0305-4c64-b319-b160ee9765a4",
       Query=[q="api-version=v2"]
       ]))
      in
       Source

       

      • THEG72's avatar
        THEG72
        Helper V
        8 years ago

        ImkeF

         

        Hi Imke, i hope you dont mind me reaching out to see if you can help me decipher this code to use relative path..I had a few goes but the code just wont work with the relative path and query with the pagination coding.

         

        The original code you helped me with is below, from my previous post i can get the refresh to work using relative path and query but when i change the code (BaseUrl) and GetTotalEntities I come accross the errors...How should i apply the relative path and query so pagination will work and refresh can still be done in service?


        Here is the unedited code which needs the relative path and query statements for Web.contents to refresh in service. Chris Webb's blog was good on this but i cant work this out with paginations.

         

        let
    BaseUrl = DataLocation & CFUID1 & "/Contact/?api-version=v2&$top=500",
    EntitiesPerPage = 500,

    GetJson = (Url) =>
        let RawData = Web.Contents(Url),
            Json = Json.Document(RawData)
        in  Json,

    GetTotalEntities = () =>
        let Json = Json.Document(Web.Contents(DataLocation & CFUID1 & "/Contact/?api-version=v2")),
            Items = Json[Count]
        in
            Items,

    GetPage = (Index) =>
        let skip = "$skip=" & Text.From(Index * EntitiesPerPage),
            Url = BaseUrl & "&" & skip
        in Url,

    EntityCount = List.Max({EntitiesPerPage, GetTotalEntities()}),
    PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
    PageIndices = {0 .. PageCount -1},
    
    URLs = List.Transform(PageIndices, each GetPage(_)),
    Pages    = List.Transform(URLs, each GetJson(_)),
    ToTable = Table.FromList(Pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandList = Table.ExpandRecordColumn(ToTable, "Column1", {"Items"}, {"Column1.Items"}),
    ExpandRecords = Table.ExpandListColumn(ExpandList, "Column1.Items"),
    TableData = Table.ExpandRecordColumn(ExpandRecords, "Column1.Items", {"UID", "CompanyName", "IsIndividual", "DisplayID", "IsActive", "Addresses", "Notes", "Identifiers", "CustomList1", "CustomList2", "CustomList3", "CustomField1", "CustomField2", "CustomField3", "CurrentBalance", "Type", "LastModified", "PhotoURI", "URI", "RowVersion", "LastName", "FirstName"}, {"UID", "CompanyName", "IsIndividual", "DisplayID", "IsActive", "Addresses", "Notes", "Identifiers", "CustomList1", "CustomList2", "CustomList3", "CustomField1", "CustomField2", "CustomField3", "CurrentBalance", "Type", "LastModified", "PhotoURI", "URI", "RowVersion", "LastName", "FirstName"}),
    RemoveNilBalances = Table.SelectRows(TableData, each [CurrentBalance] <> 0),
    MergeNames = Table.CombineColumns(RemoveNilBalances,{"FirstName", "LastName"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    CombineNames = Table.AddColumn(MergeNames, "Contact Name", each if [IsIndividual] = true then [Merged] else [CompanyName]),
    SelectColumns = Table.SelectColumns(CombineNames,{"UID", "IsIndividual", "DisplayID", "Addresses", "CurrentBalance", "Type", "Contact Name"}),
    ExpandAddressList = Table.ExpandListColumn(SelectColumns, "Addresses"),
    ExpandAddressRecords = Table.ExpandRecordColumn(ExpandAddressList, "Addresses", {"City", "State", "PostCode", "Country"}, {"City", "State", "PostCode", "Country"}),
    RemovedDuplicatedRows = Table.Distinct(ExpandAddressRecords, {"UID"}),
    AddIndexCFUID = Table.AddColumn(RemovedDuplicatedRows, "CFUID", each CFUID1)
in
    AddIndexCFUID

        So below i manage to get the base url working and refreshing with the code below...

         

        Current
BaseUrl = DataLocation & CFUID1 & "/Contact/?api-version=v2&$top=500"

New code that works
        let
         Source = Json.Document(Web.Contents("http://localhost:8080/Account/",
         [
         RelativePath="f5e76909-8427-48f1-add6-b03e3591ad43/Contact/",
         Query=[q="api-version=v2"]
         ]
         ))
        in
         Source