Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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
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
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).
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.
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?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.