none
Dynamic Parameters for Web Contents source api data RRS feed

  • Question

  • Hi Community,

    I am using Power BI/Power Query M to obtain accounting data from an API that returns a json file format.

    I am trying to dynamically update a path statement using Power query Web Contents function to obtain accounting data from a data source.

    To build the base URL i need a data location (can be local, network or web) and an company ID (datafile Unqiue resource id) which indicates the name, version, etc of the database files at that data location. 

    The entry point API below returns a list of database files (17) and 11 field records which indicate important details.

    Entry Point API shows 17 databases and 11 key fields per company file

    To parameterise the datasource and company ID, I was able to manually create a text parameters and use it in my code below.

    I could NOT however use a dynamic option like drilling down on URI or using a ADD as NEW Query option on the URI Pathing required.....?

    I can only get it to work by splitting the two and manually keying in text fields..Is there a dynamic way to do this

    = Json.Document( Web.Contents(DataLocation & SelectCompanyID & "/Contact/Supplier/?api-version=v2"))

    //This is a GET command to retrieve the Suppliers from the selected company

    Thanks again for any assistance or ideas 

    Cheers


    Wednesday, April 18, 2018 1:18 PM

Answers

  • is that a list of records? after the CountTest step, you can use a simple code like Table.FromRecords( CountTest) and that should get rid of the usage of the Table.FromList and Table.ExpandRecordColumn

    let
        // Data source for Item Ledger and conversion to Table
        Source = ItemSource,
        ToTable = Record.ToTable(Source),
    
        // Output number of records in Count and drill down on Value
        CountItems = Source[Count],
    
        // Bring Back Source Step
        SourceBack = Source,
    
        // Expand list if source count is not null otherwise dont complete next steps
        CountTest = if Source[Count] = null then null else Source[Items],
      Miguel1 = if CountTest = null then null else Table.FromRecords( CountTest)
    in
        Miguel1 


    • Edited by Miguel Escobar Saturday, April 21, 2018 6:16 PM providing the sample code
    • Marked as answer by TheG72 Sunday, April 22, 2018 12:49 AM
    Saturday, April 21, 2018 6:07 PM
  • Hey,

    I'd highly encourage you to convert your work into a Custom Connector. Trying to make things work without them (Custom Connectors) will eventually make you hit a roadblock or just waste your time trying to find workarounds to a scenario that is meant to be handled by Custom Connectors and that it'll eventually get you to create a Custom Connector out of necessity. 

    • Marked as answer by TheG72 Monday, April 23, 2018 6:13 AM
    Monday, April 23, 2018 5:21 AM

All replies

  • Hey TheG72,

    Correct me if I'm wrong, but you want to get the URI, ID, Name and such of every record in that list? if that's the case, you could transform that to a table and then expand the contents or use the Table.FromRecords function to transform all of that into a table. I'm not entirely sure what dynamic part you're trying to get, but the format that you're using for the Web.Contents function usually doesn't refresh on the Power BI Service, so you might want to take that in consideration if you ever plan on publishing that report to the Power BI Service and have it refresh on the cloud.

    Thursday, April 19, 2018 5:55 PM
  • Hi Miguel,

    Thanks for taking the time to respond, I have been reading a few of your posts and videos so thanks for contributing to Power BI world...:)

    Yes that's a good point about the refresh service.

    What i was looking to do was to build the data model so i can re use the model for different data source files by simply just changing name parameter. I would then just publish each companies model to the power bi service for people to build reports...Does that sound like a sound plan? I followed Matt Allingtons Golden Data set

    We actually had our Perth Power BI user group tonight and Matt Allington actually helped we work out what i was doing wrong...It was a simple fix but i came undone a couple of times with this formula firewall error that's been discussed recently and other issue i discussed here (operator & to types Text and List)

    The problem i did find however is sometimes the data sources don't have any data so the error handling is something i am unsure of....For example, 1 datafile has inventory and 1 data file doesnt, when you select the file that doesnt it errors out and stops the refresh as the record count is NULL

    When the refresh tries the Item LEDGER (with transactions) the Expand Step errors out as the records are null

    What is the best way to do error handling in Power Query?

    Thursday, April 19, 2018 6:22 PM
  • I think that what you're after is more of a Power BI Template rather than the process described in the blog post by Matt. 

    Here's more information about PBIT files. With a PBI file you can just open the file, enter the value of your parameter and then the report and the model will be created for you based on the value of that specific parameter or parameters.

    The process described by Matt is like the modern way of doing the Core-and-thin solution that was commonly used for Data Models uploaded to a SharePoint site and I believe one of the firsts to talk about this was Rob Collie back in the day.

    Error handling depends on your scenario and what exactly is causing the error. For your case, you could, for example, get rid of the error values with a simple "replace errors with nulls" and that should help you. There's also the variant of the IFERROR in Power Query which uses the keywords "try [this] otherwise [that]" (try otherwise) which is also pretty helpful when dealing with errors, but if you can completely rule out errors via a simple replace operation or by making sure that you don't trigger any errors, then that would be the most optimal route.

    About the Formula firewall, that main reason behind is that you're combining 2 different data sources and they have a mismatch at their privacy levels. There are other ways to trigger this, like passing a dynamic variable to a data source function, which doesn't have a privacy level defined which triggers this mismatch of privacy levels and simply outputs the formula firewall error.

    Hope this helps!

    Thursday, April 19, 2018 6:56 PM
  • Miguel, I originally thought this would save me time recreating the models for different data files with basically the same structure.

    It probably comes back to the issue that you mentioned that the Web.Contents doesn't refresh in the service. This is my first API project so i havent tried this part yet....

    I have followed Matt's process in the past but the sources were excel based so when i uploaded the model and setup a gateway for the sources it works fine with refresh and scheduling. I wrote a script to update the spreadsheets and then the service grabbed a copy of new data each time.

    Now i am directly querying the data source via a local network or local host using an API that returns json file...(This is the developer site for API) How can i sync the Power BI model in the service with the local API data from server on premise using gateway?I have looked into Power Update software, is that the only option? 

    With the error handling, its not so much an error is being returned but a step(Expand) is trying to be completed when the count is null for ItemSource query. So below this shows 26 records where as another file shows null.


    When a null is found a ITEMSOURCE, i dont wont the ItemLedger query to run as no transactions exist.

    ITEM SOURCE QUERY

    let
        Source = AccountSource,
        Items = Source[Items],
        #"Converted to Table" = Table.FromList(Items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded {0}" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"UID", "Name", "DisplayID", "Classification", "Type", "Number", "Description", "ParentAccount", "IsActive", "TaxCode", "Level", "OpeningBalance", "CurrentBalance", "BankingDetails", "IsHeader", "LastReconciledDate", "URI", "RowVersion"}, {"UID", "Name", "DisplayID", "Classification", "Type", "Number", "Description", "ParentAccount", "IsActive", "TaxCode", "Level", "OpeningBalance", "CurrentBalance", "BankingDetails", "IsHeader", "LastReconciledDate", "URI", "RowVersion"})
    in
        #"Expanded {0}"

    ITEM LEDGER QUERY

    let
        Source = ItemSource,
        Items = Source[Items],
        ToTable = Table.FromList(Items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        Expand = Table.ExpandRecordColumn(ToTable, "Column1", {"UID", "Number", "Name", "IsActive", "Description", "UseDescription", "CustomList1", "CustomList2", "CustomList3", "CustomField1", "CustomField2", "CustomField3", "QuantityOnHand", "QuantityCommitted", "QuantityOnOrder", "QuantityAvailable", "AverageCost", "CurrentValue", "BaseSellingPrice", "IsBought", "IsSold", "IsInventoried", "ExpenseAccount", "CostOfSalesAccount", "IncomeAccount", "AssetAccount", "BuyingDetails", "SellingDetails", "PhotoURI", "URI", "RowVersion"}, {"UID", "Number", "Name", "IsActive", "Description", "UseDescription", "CustomList1", "CustomList2", "CustomList3", "CustomField1", "CustomField2", "CustomField3", "QuantityOnHand", "QuantityCommitted", "QuantityOnOrder", "QuantityAvailable", "AverageCost", "CurrentValue", "BaseSellingPrice", "IsBought", "IsSold", "IsInventoried", "ExpenseAccount", "CostOfSalesAccount", "IncomeAccount", "AssetAccount", "BuyingDetails", "SellingDetails", "PhotoURI", "URI", "RowVersion"})
    in
        Expand

    ITEM SOURCE WITH NO RECORDS FOUND NULL AT COUNT

    So then when the Item Ledger runs it trys to expand and cant find Column1

    Hopefully, i have made the issue a bit clearer....thanks for you thoughts on the matter.!

    Thursday, April 19, 2018 7:26 PM
  • I'd encourage you to create a custom connector based on the work that you already have. 

    Since you're basically querying an API, it would be more optimal if there was a specific custom connector handling everything especially with things like pagination from the API. Usually that NextPageLink field is used for pagination and not really for analytics, and the github repo that Microsoft has for Custom Connector already has a sample to deal with pagination. You could, for example, navigate to the specific "Items" List and do a count on that to see if the list is empty. If it is then you can stop the query and go directly to a static output of your choice or something different, but I wouldn't try doing the Expand operation. Just navigating directly to that list and seeing if its empty or not.

    Thursday, April 19, 2018 10:27 PM
  • Yes, i am basically querying the API which is on an on premise server. With this particular accounting package you can have your data file stored on the providers cloud or on a local server.

    In this case, i am building data models for a on premise based data source via an API that returns a Json or xml format.

    The custom connector, pagination and the stop and skip steps will probably be another project for some one with more skills than me...

    Is project something you can do or know where i can get this part completed?

    I would agree if I can review all the sources file counts, i could then determine whether the expand routine is required or not for each ledger in the source company file.

    Thanks for the links and input!




    • Edited by TheG72 Friday, April 20, 2018 7:52 AM
    Friday, April 20, 2018 12:21 AM
  • you can stop the query and go directly to a static output of your choice or something different, but I wouldn't try doing the Expand operation. Just navigating directly to that list and seeing if its empty or not.

    As i am using a parameter to select the company file the count will switch between different source files.

    Company  A sells items and has 38.

    Company A has 38 stock items

    Company B has no item records as it sells services and not stock.

    Company B has null count for stock item records

    How can i write the proper M syntax into my reference query to stop it? I am not sure how to bring the count <> null reference into my code or use the if try or otherwise statement for the item ledger query below..

    let
        Source = ItemSource,
        Items = Source[Items],
        ToTable = Table.FromList(Items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        Expand = Table.ExpandRecordColumn(ToTable, "Column1", {"UID", "Number", "Name", "IsActive", "Description", "UseDescription", "CustomList1", "CustomList2", "CustomList3", "CustomField1", "CustomField2", "CustomField3", "QuantityOnHand", "QuantityCommitted", "QuantityOnOrder", "QuantityAvailable", "AverageCost", "CurrentValue", "BaseSellingPrice", "IsBought", "IsSold", "IsInventoried", "ExpenseAccount", "CostOfSalesAccount", "IncomeAccount", "AssetAccount", "BuyingDetails", "SellingDetails", "PhotoURI", "URI", "RowVersion"}, {"UID", "Number", "Name", "IsActive", "Description", "UseDescription", "CustomList1", "CustomList2", "CustomList3", "CustomField1", "CustomField2", "CustomField3", "QuantityOnHand", "QuantityCommitted", "QuantityOnOrder", "QuantityAvailable", "AverageCost", "CurrentValue", "BaseSellingPrice", "IsBought", "IsSold", "IsInventoried", "ExpenseAccount", "CostOfSalesAccount", "IncomeAccount", "AssetAccount", "BuyingDetails", "SellingDetails", "PhotoURI", "URI", "RowVersion"})
    in
      Expand


    Friday, April 20, 2018 9:40 AM
  • Do a right click on the number 38 that you see. Then do a drill down (select the option that reads Drill down). Based on the step that it creates, you can write a simple formula like "if [name of step] = null then null else [navigate to the Items]" 
    Friday, April 20, 2018 4:23 PM
  • Thanks Miguel, i ended up putting this code in but my If test is not stopping the command when item count is null can see what the issue is? Do i need some other syntax in the reference?

    let
        // Data source for Item Ledger and conversion to Table
        Source = ItemSource,
        ToTable = Record.ToTable(Source),
    
        // Output number of records in Count and drill down on Value
        CountItems = Source[Count],
    
        // Bring Back Source Step
        SourceBack = Source,
    
        // Expand list if source count is not null otherwise dont complete next steps
        CountTest = if Source[Count] = null then null else Source[Items],
        ToTableData = Table.FromList(CountTest, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        Expand = Table.ExpandRecordColumn(ToTableData, "Column1", {"UID", "Number", "Name", "IsActive", "Description", "UseDescription", "CustomList1", "CustomList2", "CustomList3", "CustomField1", "CustomField2", "CustomField3", "QuantityOnHand", "QuantityCommitted", "QuantityOnOrder", "QuantityAvailable", "AverageCost", "CurrentValue", "BaseSellingPrice", "IsBought", "IsSold", "IsInventoried", "ExpenseAccount", "CostOfSalesAccount", "IncomeAccount", "AssetAccount", "BuyingDetails", "SellingDetails", "PhotoURI", "URI", "RowVersion"}, {"UID", "Number", "Name", "IsActive", "Description", "UseDescription", "CustomList1", "CustomList2", "CustomList3", "CustomField1", "CustomField2", "CustomField3", "QuantityOnHand", "QuantityCommitted", "QuantityOnOrder", "QuantityAvailable", "AverageCost", "CurrentValue", "BaseSellingPrice", "IsBought", "IsSold", "IsInventoried", "ExpenseAccount", "CostOfSalesAccount", "IncomeAccount", "AssetAccount", "BuyingDetails", "SellingDetails", "PhotoURI", "URI", "RowVersion"})
    in
        Expand


    Expression Error: We cannot convert the value null to type List
    Details:
    Value=
    Type=Type




    • Edited by TheG72 Saturday, April 21, 2018 1:54 AM edit
    Saturday, April 21, 2018 1:34 AM
  • is that a list of records? after the CountTest step, you can use a simple code like Table.FromRecords( CountTest) and that should get rid of the usage of the Table.FromList and Table.ExpandRecordColumn

    let
        // Data source for Item Ledger and conversion to Table
        Source = ItemSource,
        ToTable = Record.ToTable(Source),
    
        // Output number of records in Count and drill down on Value
        CountItems = Source[Count],
    
        // Bring Back Source Step
        SourceBack = Source,
    
        // Expand list if source count is not null otherwise dont complete next steps
        CountTest = if Source[Count] = null then null else Source[Items],
      Miguel1 = if CountTest = null then null else Table.FromRecords( CountTest)
    in
        Miguel1 


    • Edited by Miguel Escobar Saturday, April 21, 2018 6:16 PM providing the sample code
    • Marked as answer by TheG72 Sunday, April 22, 2018 12:49 AM
    Saturday, April 21, 2018 6:07 PM
  • Web.Contents function usually doesn't refresh on the Power BI Service, so you might want to take that in consideration if you ever plan on publishing that report to the Power BI Service and have it refresh on the cloud.

    Hi Miguel, You mentioned the issue before about Web.Contents no refreshing in Power BI service

    Is it possible to adopt the fix mentioned by Chris Webb with Relative path and static first query?

    My web.contents query is as follows:

    let
        Source =
            Json.Document(
                Web.Contents(SelectedCompany&"/GeneralLedger/GeneralJournal/"&APIVersion))
    in
        Source
     

    I have a parameter to SelectCompany this outputs a text field like:

    "http://localhost:8080/AccountRight/3a6508db-8650-403c-ad9d-6d602e3ac1ae" which is the first part of the query path.

    The next part is a static text string "/GeneralJournal/" is the end ledger which can change depending on what ledger is required.

    The final part is the text parameter for the API version which outputs a text field "?api-version=v2".

    Is there a way to over come this issue using Chris's option of relative path?

    This seems to be getting harder and harder to get a result using API source...

    I probably need to follow your advice and get this connector built....I also need to obtain all the records through the pagination so it sounding a bit more difficult each time.

    Are there any options you can suggest?

    Sunday, April 22, 2018 11:48 PM
  • Hey,

    I'd highly encourage you to convert your work into a Custom Connector. Trying to make things work without them (Custom Connectors) will eventually make you hit a roadblock or just waste your time trying to find workarounds to a scenario that is meant to be handled by Custom Connectors and that it'll eventually get you to create a Custom Connector out of necessity. 

    • Marked as answer by TheG72 Monday, April 23, 2018 6:13 AM
    Monday, April 23, 2018 5:21 AM
  • OK thanks for the suggestions and help with this Topic Miguel. I will try and see if I can build the connector or modify the one published in the service by software vendor

    Cheers Again.

    Monday, April 23, 2018 6:13 AM