Automate weekly API call

[Anonymous]

Currently I am doing manual work of the below tasks:
1. Duplicate existing table and modify the date in advanced editor to reflect that week date.

2. Duplicate the measure which is based on that newly created table and add the date there as well.

Both these actions account to getting data from service now and update a lot of other metrics. I am wondering this process can be automated?
Mar 28th
April 4
April 11


let
Source = Json.Document(Web.Contents("https://MyOrg.service-now.com/api/now/table/incident?sysparm_query=sys_created_on%3C%3Djavascript%3A... the groups)
&sysparm_display_value=true&sysparm_fields=sys_created_on%2Cassignment_group%2Cresolved_at&sysparm_limit=50000")),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded result" = Table.ExpandListColumn(#"Converted to Table", "result"),
#"Expanded result1" = Table.ExpandRecordColumn(#"Expanded result", "result", {"assignment_group", "sys_created_on", "resolved_at"}, {"result.assignment_group", "result.sys_created_on", "result.resolved_at"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded result1",{{"result.assignment_group", type any}, {"result.sys_created_on", type datetime}, {"result.resolved_at", type datetime}}),
#"Expanded result.assignment_group" = Table.ExpandRecordColumn(#"Changed Type", "result.assignment_group", {"display_value"}, {"display_value"}),
#"Added Custom" = Table.AddColumn(#"Expanded result.assignment_group", "WeekDate", each "04Apr2022")
in
#"Added Custom"
 

[Anonymous]

Thank you.
We decided that automatic creation of table is not possible and hence we decided to automate dates in the API call for existing tabe to keep getting last 8 weeks of data.

[Anonymous]

Having the date as variable/parameter is understandable. How do I get the new data into a new table automatically?
Is there a way to acheive weekly new API call and store the results in a new table named with that weekname like Incdentsbefore6Apr

[Vijay_A_Verma]

A query can't create another query to push the data into another table. Once data is loaded, you can create a reference query, load the result to some place say Database server, Excel etc and then break the connection of this reference query by deleting the reference query. 

[Vijay_A_Verma]

1. In PQ, you can use parameter to store your date value. Then in this step

#"Added Custom" = Table.AddColumn(#"Expanded result.assignment_group", "WeekDate", each "04Apr2022")

replace "04Apr2022" with parameter name.

2. Measures can't refer to parameters directly. You will have to store this parameter value in another query. You can simply have a query having one cell where its value will be equal to that parameter value.

Then you can can refer to this table value in your measure.