Incremental refresh with Large Data using Parameter_failure or not working properly

[Harish_27]

Hi All,

i'm implementing incremental refresh one of dashboard which has around 400 million Data in single table, while implementing incremental refresh the initial load all the data will load after implementing filter condition on date column using parameters right?. 
it may take morre time to load in desktop so to avoid that i proceeded one approach like,

let
DevQuery = "Select Top 100 * FROM [cs_lat_mex_gold].[SF_HEX2_6B_Table]",
ProdQuery ="select * FROM [cs_lat_mex_gold].[SF_HEX2_6B_Table]",

Condition = if Environment = "Dev" then DevQuery else ProdQuery,

Source = Sql.Database("pepreportanddashboardssynapsesdev.sql.azuresynapse.net", "Sample", [Query=Condition]),
cs_lat_mex_gold_SF_HEX2_6B_Table = Source,
#"Changed Type" = Table.TransformColumnTypes(cs_lat_mex_gold_SF_HEX2_6B_Table,{{"Sales", type number}, {"PMF_Shares", type number}, {"Month", Int64.Type}, {"Week", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [ingestion_date] < RangeEnd and [ingestion_date] >= RangeStart)
in
#"Filtered Rows"




so it will reduce the load in desktop and we can load entire data in service through gateway. (Note: Policy not defined).

after loading entire data i will download the file again and i will apply the policy like below.

Native query got disabled due to this approach, but checked in applied steps there is no transformation steps applied, native query was enabled previous step but in in filter step it got disabled.

 

after implementing all and publishing in service the refresh time was taking more than full refresh and also some time refresh got failing with Mashup error

can you please help me on this is this approcah was correct or not also give me some suggestion how to proceed for the large volume data in single table for incremental refresh

[mohitkumawat · Accepted solution]

Hi @Harish_27 ,

1. Whenever a Native query is disabled at the last applied steps.
2. When applying the policy, ensure that the error mentioned below does not occur.

3. Dev and Prod Parameters not work with incrementel Refresh because it Disable native query.
4.  If your ingestion_date doesn't contain a column of Date/Time data type.

Remove the Development and Production parameters. Adjust the Range Start and Range End dates to encompass the last 15 days based on your data. Load this modified dataset into Power BI Desktop and apply the necessary policies. See the screenshot below for reference.

https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview#create-paramete...