Forum Discussion
Incremental refresh messing up with query calculation
I don't see the Incremental Refresh connection to your query. Please clarify.
Sorry, missed to explain that part.
When I publish the report without incremental refresh setup (so, just whatever is in the startdate-enddate range), it works and calculates properly.
The random surpluss dates show when I publish the report with incremental refresh, this setup:
Data is sourced from Azure Databricks, if it makes difference.
Show the part of your Power Query that applies the filters.
NOTE: RangeStart must be inclusive, RangeEnd must be exclusive (or the other way round). They cannot both be inclusive.
Fixed that yesterday 🙂
Here's the whole code; I've just manually replaced some stuff like names etc so please ignore if I made some typo. Full structure is herelet
Source = Databricks.Catalogs("some_numbers.azuredatabricks.net", "/sql/1.0/warehouses/some_code", [Catalog=null, Database=null, EnableAutomaticProxyDiscovery=null]),
hive_metastore_Database = Source{[Name="hive_metastore",Kind="Database"]}[Data],
schema_name_Schema = hive_metastore_Database{[Name="schema_name",Kind="Schema"]}[Data],
table_name_Table = schema_name_Schema{[Name="table_name",Kind="Table"]}[Data],
#"Incremental Refresh Filter" = Table.SelectRows(table_name_Table, each [calday] >= Date.From(RangeStart) and [calday] < Date.From(RangeEnd)),
#"Filtered Rows1" = Table.SelectRows(#"Incremental Refresh Filter", each [something] = "code1" or [something] = "code2"),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1",{{"material", Int64.Type}, {"aedat", type datetime}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{columnnames}),#"GroupedData" = Table.Group(#"Removed Columns", {"customer", "fiscper"},
{
{"MaxCalday", each List.Max([calday]), type date}
}
),
#"MergedData" = Table.Join(#"Removed Columns", {"customer", "fiscper", "calday"}, GroupedData, {"customer", "fiscper", "MaxCalday"}),
#"GroupedData2" = Table.Group(#"MergedData", {"customer", "MaxCalday"},
{
{"MaxId", each List.Max([some_id]), type text}
}
),
#"MergedData2" = Table.Join(#"MergedData", {"customer", "MaxCalday", "some_id"}, GroupedData2, {"customer", "MaxCalday", "MaxId"}),in
#"MergedData2"each [calday] >= Date.From(RangeStart) and [calday] < Date.From(RangeEnd)),Flip this around to say
each DateTime.From([calday]) >= RangeStart and DateTime.From([calday]) < RangeEnd),