none
Construct and API call with a dynamic start and end date RRS feed

  • Question

  • Goal: To create a PowerBI Data Source that makes an APICall for a dynamic data range.  

    Example: 

    Tansform this call: 

    =Xml.Tables(Web.Contents("https://endpoint?StartDate=2018-01-01&StopDate=2018-01-31&Response=xml"))

    Into:

    =Xml.Tables(Web.Contents("https://endpoint?StartDate={StartOfLastMonth}&StopDate={EndOfLastMonth}&Response=xml"))

    I've tried creating Tables with the date values I'm after. I've created parameters of type Query assigned to those tables (or Lists) but the parameter's Current Value always seems to get passed to the query.  I've tried Injecting M-Query expressions directly into the URL, but that seems to cause an error. 

    I must have looked at 30 similar questions so far, but I can't find a single answer that seems to solve a pretty straight forward problem: Creating a parameterized query to execute. 

    Friday, February 2, 2018 11:38 PM

Answers

  • Curly brackets are not the solution here. Instead you have to escape the URL-string and squeeze in the parameters with an ampersand like this:

    =Xml.Tables(Web.Contents("https://endpoint?StartDate=" & StartOfLastMonth & " &StopDate=" & EndOfLastMonth & " &Response=xml"))


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Saturday, February 3, 2018 9:13 PM
    Moderator