Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
THEG72
Helper V
Helper V

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!

 

8 REPLIES 8
GilbertQ
Super User
Super User

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

@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

 

@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 

 

Not sure if I got your question right, but wouldn't you "just" replace the occurrences of the concatenations that make the problem:

 

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

 

by the new code?:

 

"http://localhost:8080/Account/",  [ RelativePath="f5e76909-8427-48f1-add6-b03e3591ad43/Contact/",  Query=[q="api-version=v2"] ] ))

effectively like so then?:

 

let
    BaseUrl = "http://localhost:8080/Account/",  [ RelativePath="f5e76909-8427-48f1-add6-b03e3591ad43/Contact/",  Query=[q="api-version=v2"] ] ,
    EntitiesPerPage = 500,

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

    GetTotalEntities = () =>
        let Json = Json.Document(Web.Contents("http://localhost:8080/Account/",  [ RelativePath="f5e76909-8427-48f1-add6-b03e3591ad43/Contact/",  Query=[q="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

Hope this is what you meant 🙂

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi @ImkeF thanks for your reply...I cant get the format for the query and relative path for Web.Contents.

 

How should i format this below for the Relative Path and Query

 

let
    Source =
        Json.Document(
            Web.Contents(
                "http://localhost:8080/Account/",
    [RelativePath="0f7376cf-c30a-49d1-8855-8501e3081714/Contact/?api-version=v2&$top=1000"])),
    Items = Source[Items]
in
    Items

I got this code to work  below and it outputs all 999 records...

 

let
    Source =
        Json.Document(
            Web.Contents(
                "http://localhost:8080/Account/",
        [
            RelativePath="0f7376cf-c30a-49d1-8855-8501e3081714/Contact/",
            Query=
            [
                q="api-version=v2",
                Stop="1000"
            ]
        ]
        ))
in
    Source

I now move this into the code but i get error TOke Indentifier expected at the [RelativePath open bracket..can you see what i am missing?

 

let
    BaseUrl = "http://localhost:8080/Account/", [RelativePath="0f7376cf-c30a-49d1-8855-8501e3081714/Contact/", Query= [q="api-version=v2", Stop="1000"] ],

    EntitiesPerPage = 1000,

Hard to say from the distance, but having a look at your function "GetPage" I would assume that this will not work, as it is an operation to work on a string and I believe that you have to adjust the integration to the relative path record there.

 

To debug I suggest that you try to execute each of your functions on their own, passing in the content that would come from the query.

 

If this doesn't help I would need to know in which step of your query the error occurs and please paste screenshots for the error message, thanks!

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@ImkeF

 

Thanks for the line by line suggestion...the "Pages = List.Transform(URLs, each GetJson(_))" line gives an error in output..

 

It also doesnt look like i have constructed my URL code correctly in previous step (URL's).

 

Error getting Page URL'sError getting Page URL's

Here is code to date

 

let
    BaseUrl = "http://localhost:8080/AccountRight/",

// [RelativePath="0f7376cf-c30a-49d1-8855-8501e3081714/Contact/?api-version=v2&$top=1000"])),

   EntitiesPerPage = 1000,

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

    GetTotalEntities = () =>
        let Json = Json.Document(Web.Contents("http://localhost:8080/AccountRight/",
            [RelativePath="0f7376cf-c30a-49d1-8855-8501e3081714/Contact/?api-version=v2&$top=1000"])),
            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(_))
in 
    Pages
/* 
    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)
*/

The first item in the URL list step is ...Looks like my apiversion and stop skip is not correct.

http://localhost:8080/AccountRight/&$skip=0

 

It should be like 

http://localhost:8080/AccountRight/d2c78098-1f9c-4334-aee9-27349b3ed6a4/GeneralLedger/JournalTransaction/?api-version=v2&$top=1000&$skip=0

 

So this probably relates to what you said about the integrataion on relative path...and the query parameter which i am not using in this loop.

 

@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 worksGateway connection worksTESTapi2.PNG

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors