Forum Discussion
Help with multiple url query parameters WITH token to avoid scheduled refresh issue in Service
Dear community, I need help with the below, I could not find a solution online using relativepath. See the query below. I'm connection to the api and getting the authorisation token. If I leave my 2 parameters as static ("created_after=2024-05-01&records_per_page=1000") I am able to refresh in the service with no issues however each month I would need to manually update the date for it to not fail. Is there any way to create a parameter that would pull in the current date as of the day and also let me use the 'records_per_page=1000' parameter? Sincerely appreciate any help with this, thanks so much.
let
Headers = [#"Authorization"=AccessTokenHeader]
]
),
FormatAsJsonQuery = Json.Document(GetJsonQuery),
#"Converted to Table" = Table.FromList(FormatAsJsonQuery, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "siteId", "siteName", "yourRef", "address", "description", "additionalDetail", "status", "createdBy", "createdDate", "reportedBy", "reportedDate", "incidentDate", "investigation", "mainType", "onsiteLocation", "immediateControlMeasures", "additionalIncidentTypes", "reportedToRegulator"}, {"id", "siteId", "siteName", "yourRef", "address", "description", "additionalDetail", "status", "createdBy", "createdDate", "reportedBy", "reportedDate", "incidentDate", "investigation", "mainType", "onsiteLocation", "immediateControlMeasures", "additionalIncidentTypes", "reportedToRegulator"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"createdDate", type datetime}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"createdDate", type date}})
in
#"Changed Type1"
Hi Andrew_BI_1 ,
Please do not include sensitive information in your code!
Create a parameter in Power Query that holds the current date.
let CurrentDate = DateTime.Date(DateTime.LocalNow()) in CurrentDateModify an existing query:
let ...... AccessTokenHeader = "bearer " & AccessToken, CurrentDate = DateTime.ToText(DateTime.Date(DateTime.LocalNow()), "yyyy-MM-dd"), QueryString = "created_after=" & CurrentDate & "&records_per_page=1000", GetJsonQuery = Web.Contents(https://api.datastation.co/api/ & "incident/filter?" & QueryString, [Headers = [#"Authorization"=AccessTokenHeader]]), FormatAsJsonQuery = Json.Document(GetJsonQuery), ...... in #"Changed Type1"If you have any other queries please feel free to contact me.
Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
1 Reply
Hi Andrew_BI_1 ,
Please do not include sensitive information in your code!
Create a parameter in Power Query that holds the current date.
let CurrentDate = DateTime.Date(DateTime.LocalNow()) in CurrentDateModify an existing query:
let ...... AccessTokenHeader = "bearer " & AccessToken, CurrentDate = DateTime.ToText(DateTime.Date(DateTime.LocalNow()), "yyyy-MM-dd"), QueryString = "created_after=" & CurrentDate & "&records_per_page=1000", GetJsonQuery = Web.Contents(https://api.datastation.co/api/ & "incident/filter?" & QueryString, [Headers = [#"Authorization"=AccessTokenHeader]]), FormatAsJsonQuery = Json.Document(GetJsonQuery), ...... in #"Changed Type1"If you have any other queries please feel free to contact me.
Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!