I have built an custom Power Query Connector to fetch the data from the api whose payload is shown below
[
orderBy = {[created_at = "desc"]},
select = [
archive_date = true,
archived = true,
assignee_id = true,
assignment_date = true,
created_at = true,
data_id = true,
deleted_at = true,
description = true,
hash = true,
id = true,
metadata = true,
risk_score = true,
service = true,
severity = true,
status = true,
tags = true,
updated_at = true,
user_severity = true
],
skip = skip,
take = take,
withDataMessage = true,
countOnly = is_count,
where = [
created_at = [gte = DateTime.ToText(gte, "yyyy-MM-ddTHH:mm:ss+00:00"), lte = DateTime.ToText(lte, "yyyy-MM-ddTHH:mm:ss+00:00")],
status = [in = {"VIEWED", "UNREVIEWED", "CONFIRMED_INCIDENT", "UNDER_REVIEW", "INFORMATIONAL"}]
]
]
What I need is there are two parameters gte and lte by configuring which I get the data between these two intervals so I somehow need to pass this gte and lte for every scheduled refresh to get the new data .
But What I have noticed is every time we run the scheduled refresh the custom power Query connector code is running again with the initial input provided by the user and I am not able to pass these two parameters every time when I do the scheduled refresh .
Is there any way we can pass the custom parameters to the custom power Query connector every time when we run a refresh ?
Ideal flow what I need is
1. First I will prompt the user to enter the API Key and then Number of Days and Fetch frequency
2. Let the input be like API Key = ******* , Number of Days = 5 days, fetch frequency is 30min let the time stamp when we are doing inital fetch is 18 JAN 10 AM
3. Now the user has configured scheduled refresh for every 45min in power BI
4. So in the intial fetch I must be able to get the data of last 5 days like the data from 13JAN 10 AM to 18JAN 10 AM and when ever the scheduled refresh runs I need to get the data from 18 JAN 10AM(last_fetched_time) to 18JAN 10:30AM and this data has to be appended with the data that is fetched from 5 days .
This is how the flow must look like and also ..
If the next refresh happends as scheduled the data now must be from 18 JAN 10:30AM(last_fetch) to 18JAN 11AM and it must be appended to previous data so every time the scheduled refresh run I need to get data from last_fetched_timestamp to last_fetched_timestamp + fetch frequency and add the recently fetched data to the previous data .
How can I achieve this with my custom Power Query connector that I have built . The problem I am facing is that every time the refresh is running
1. It is running the same initial code of Query connector so I was not able to change gte and lte parameters in payload to get the data in that time frame .
Is there any way to persist data between the two refreshes in the power BI and also is there any way to store the data in the table from the previous refresh and read this data through M code written in the custom power Query Connector at the next refresh .
and also How can I know in my custom power query connector whether the connector code is running for the first time or else it is doing the refresh ?
Please help me I need help ASAP?
Thank you
Kind regards,
Asif.
