Web Query with multiple date ranges

[patelaz]

I have a REST API query that I need to load into Power BI and the API limits data in 180 day increments per API call.  If I have over 2 years of data to load, I thought I could create a table called TRIMESTERS with a list of various date ranges such as this:

How do I reference the above dates in the query below for the saleTimestampRange filter?

let
Source = Xml.Tables(Web.Contents("https://xxxxxxx/recurringservices?" & "saleTimestampRange=2018-05-01", [Headers=[app_id="xxxxxxx", app_key="xxxxxxx"]])),
Table2 = Source{2}[Table],
Table0 = Table2{0}[Table],
#"Changed Type" = Table.TransformColumnTypes(Table0,{{"recurringServiceId", type text}, {"createTimestamp", type datetime}, {"memberId", type text}, {"agreementNumber", Int64.Type}, {"memberFirstName", type text}, {"memberLastName", type text}, {"salesPersonId", type text}, {"salesPersonFirstName", type text}, {"salesPersonLastName", type text}, {"recurringServicePlanId", type text}, {"recurringServiceStatus", type text}, {"recurringServiceSubStatus", type text}, {"recurringTypeDesc", type text}, {"numberBilled", Int64.Type}, {"totalPeriods", type text}, {"invoiceTotal", Int64.Type}, {"frequency", type text}, {"reorderQuantity", Int64.Type}, {"serviceItem", type text}, {"unitPrice", Int64.Type}, {"autoRenew", type logical}, {"purchasedClub", Int64.Type}, {"serviceEmployeeId", type text}, {"serviceEmployeeFirstName", type text}, {"serviceEmployeeLastName", type text}, {"campaignName", type text}}),
#"Expanded recurringServiceDates" = Table.ExpandTableColumn(#"Changed Type", "recurringServiceDates", {"saleDate", "firstBillingDate", "inactiveDate", "deactivateReason", "lastModifiedTimestamp", "nextBillingDate"}, {"recurringServiceDates.saleDate", "recurringServiceDates.firstBillingDate", "recurringServiceDates.inactiveDate", "recurringServiceDates.deactivateReason", "recurringServiceDates.lastModifiedTimestamp", "recurringServiceDates.nextBillingDate"}),
#"Expanded commissionsEmployeeIds" = Table.ExpandTableColumn(#"Expanded recurringServiceDates", "commissionsEmployeeIds", {"commissionsEmployeeId"}, {"commissionsEmployeeIds.commissionsEmployeeId"})
in
#"Expanded commissionsEmployeeIds"

[Mariusz · Accepted solution]

Hi @patelaz 

You can convert your API Query into a function, something like below.

( dates as text ) => let
    Source = Xml.Tables(Web.Contents("https://xxxxxxx/recurringservices?saleTimestampRange=" & dates, [Headers=[app_id="xxxxxxx", app_key="xxxxxxx"]])),
    Table2 = Source{2}[Table],
    Table0 = Table2{0}[Table]
in 
    Table0

 And later In your dates table go to Add Column ribbon >  Invoke Custom Funktion and adjust the setting to reflect the below.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.