Forum Discussion
Incremental Refresh failing
The issue you're encountering while setting up incremental refresh in Power BI appears to stem from differences in how Power BI handles query folding and schema recognition between Desktop and the Power BI Service. While everything works locally—because Power BI Desktop executes the full query in-memory—it fails in the Service, which relies on the data source's query folding capabilities and often interprets the M code more strictly. The error message suggests that Power BI Service is expecting a column named
Column1orColumn1.ecoli, but cannot find it in the returned dataset from your API. This could be due to a few reasons: the structure of the API response might be dynamic (i.e., column names change or appear conditionally), or the use of hardcoded column names in your M code doesn't match what's returned at runtime in the Service. Also, if your incremental logic relies on relative date filters or dynamic parameters (likeRangeStartandRangeEnd), and the schema isn't fully defined before those filters are applied, the Service might not be able to validate the final output. To fix this, ensure that the column names are stable and consistent, explicitly define your schema usingTable.TransformColumnTypesorTable.SelectColumns, and test your queries step-by-step using "View Native Query" to confirm folding. Also, avoid referencing nested fields unless they've been expanded and renamed clearly. Debugging this often involves simplifying the query to just return a few rows, publishing it, and gradually reintroducing complexity until the error appears again.
The issue you're encountering while setting up incremental refresh in Power BI appears to stem from differences in how Power BI handles query folding and schema recognition between Desktop and the Power BI Service. While everything works locally—because Power BI Desktop executes the full query in-memory—it fails in the Service, which relies on the data source's query folding capabilities and often interprets the M code more strictly. The error message suggests that Power BI Service is expecting a column named Column1 or Column1.ecoli, but cannot find it in the returned dataset from your API. This could be due to a few reasons: the structure of the API response might be dynamic (i.e., column names change or appear conditionally), or the use of hardcoded column names in your M code doesn't match what's returned at runtime in the Service. Also, if your incremental logic relies on relative date filters or dynamic parameters (like RangeStart and RangeEnd), and the schema isn't fully defined before those filters are applied, the Service might not be able to validate the final output. To fix this, ensure that the column names are stable and consistent, explicitly define your schema using Table.TransformColumnTypes or Table.SelectColumns, and test your queries step-by-step using "View Native Query" to confirm folding. Also, avoid referencing nested fields unless they've been expanded and renamed clearly. Debugging this often involves simplifying the query to just return a few rows, publishing it, and gradually reintroducing complexity until the error appears again.
Hi Poojara_D12,
I have attempted your suggested but not quite sure if I am doing it correctly as I broke the data. If it helps I'll send the steps I have applied and see if you can guide me to the right setup:
let
datetimenow = RangeEnd,
datetimefrom = RangeStart,
baseURI =,
baseTokenURI =,
headers=,
postData=,
formData = Text.ToBinary(postData),
GetJson = Web.Contents(baseTokenURI,
[
Headers = headers,
Content = formData]),
FormatAsJson = Json.Document(GetJson),
access_token = Text.From(FormatAsJson[access_token]),
parms="{""dateRange"":{""dateFrom"":""" & DateTime.ToText(datetimefrom, "yyyy-MM-ddTHH:mm:ss.000Z") & """,""dateTo"":""" & DateTime.ToText(datetimenow, "yyyy-MM-ddTHH:mm:ss.000Z") & """}}",
GetJsonQuery = Web.Contents(baseURI,
[Headers =[Authorization="Bearer "&access_token, ContentType="application/json"],
RelativePath,
Query
=
[
criteria = parms
]]),
FormatAsJsonQuery = Json.Document(GetJsonQuery),
data = FormatAsJsonQuery[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Table" = if Table.IsEmpty(#"Converted to Table") then
Table.FromRecords({[]}) else Table.ExpandRecordColumn(#"Converted to Table", "Column1", { .....}),
,#"Added Custom" = Table.AddColumn(#"Expanded Table", "Custom logic"),
#"Renamed Columns" =,
#"Changed Type" = Table.TransformColumnTypes
in
#"Changed Type"