Append Data

[stribor45]

I have this two queries. One contains data from Jan 2023 until June 2023 and the second pulls data from July 1 until now.  Both of these are done via API to an external database. When I do refresh both of these are executed. how can I prevent #1 from running and just keep appending the results of #2?

Thank you

[stribor45]

@TomMartens I couldn't follow that tutorial so I found some other resources online and was able to set it up in Power Bi Desktop.

1. I have one table of data that contains column with the datetime
2. I created RangeStart and RangeEnd parametars
3. Filtered data based on #2
4. Refreshed in Power BI Desktop and everything works fine
5. Published to Power BI Service and receiving error

Error received is "This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources"

After some reading on that link and following Chriss Webb Article

I adjusted my API call to include relativepath and query parametars in Web.Contents() call but error still remains. Is there a way to see mor einformation about actual error in power bi service?

Thank you

[TomMartens]

Hey @stribor45,

Power BI Service sets the values of both parameters RangeStart and RangeEnd when the Service initiates refresh. Because APIs do not support query folding filtering must happen immediately. Please be aware that the incremental refresh happens on the Service.

Hopefully, this explains the filter step.I recommend reading this article: https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview

Maybe you also want to split the pbix into a data pbix and a content pbix. The data pbix defines the dataset and holds the incremental refresh definition. The content pbix contains the data visualizations, but instead of connecting to the API this pbix connects to the Power BI dataset that becomes available after the data pbix was published to the Power BI Service.

Regards,

Tom

[stribor45]

looks like there is a problem with the path used to connect to an external source via API

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

[stribor45]

ok after I followed the steps I published it to service but when I refresh there I get this error.  i followed the link provided in error and apparently since this is a dynamic data source it can't be refreshed in the power bi service. "Some data sources may not be listed because of hand-authored queries. If that warning is present in the Data Source Settings dialog that appears, then a dynamic data source that can't be refreshed in the Power BI service is present." 

The tutorial above also has API call as well but somehow it managed to refresh. Any suggestions?

[stribor45]

@TomMartens I tried to follow his example but can't really track what he is doing. The downloaded example doesn't work. What is he doing here in the configuration of the data table? Is that the table that contains the data? If it is why is there only those two dates?

[TomMartens · Accepted solution]

Hey @stribor45 ,

you can't because this is how it works. You can consider using incremental refresh, even if API-based data sources are not supporting incremental by default. Start reading everything @GilbertQ wrote about incremental refresh: https://www.fourmoo.com/2020/07/01/how-to-configure-incremental-refresh-with-power-query-in-power-bi...

Hopefully, this provides enough to get you started.

Regards,

Tom

[stribor45]

@TomMartens  why is he filtering based on these two parameters right after he creates them?