Incremental Refresh failing

[AmazingRandom]

I am trying to set up an incremental refresh for a dashboard that has a massive load of data for our API to handle within its 1 hour time out limit. I managed to set up paramaters and it seems I can load it and refresh it locally, but when trying to refresh on demand in the PBI workspace it gives messages (I have been trying to tweak the M code to get it to work but no luck) like:

message:Expression.Error: The column 'Column1' of the table wasn't found.. Column1. . The exception was raised by the IDbCommand interface.

Or 

.Error: The column 'Column1.ecoli' of the table wasn't found.. Column1.ecoli. . The exception was raised by the IDbCommand interface.

This is my first time doing this so I am probably not aware of a fix or seeing the issue straight on, tahnks in advance for the help!

[v-priyankata]

Hi @AmazingRandom 

Thank you for reaching out to the Microsoft Fabric Forum Community.

Whatever suggested by @Poojara_D12  is correct. @@Poojara_D12 Thank you for your inputs. They are helpful.
@AmazingRandom When you're using Folder or SharePoint Folder connectors with Incremental Refresh, Power BI doesn’t just look at your main query — it also depends on the steps inside the sample file query. If important steps like Promoted Headers or Changed Type aren’t set up there, the refresh in the Power BI Service can fail with errors like “column not found,” even if everything works perfectly in Desktop.
Please find below Documents it may help you.
Solved: Incremental refresh - Expression.Error: The column... - Microsoft Fabric Community
Troubleshoot incremental refresh and real-time data - Power BI | Microsoft Learn

If this information is helpful, please “Accept as solution” to assist other community members in resolving similar issues more efficiently.
Thank you.

[v-priyankata]

Hi @AmazingRandom 

I hope the information shared was helpful to you. If your question has been answered, kindly mark the most relevant reply as the Accepted Solution. This small action can make a big difference for others who are looking for the same solution.

[Poojara_D12 · Accepted solution]

Hi @AmazingRandom 

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.

[AmazingRandom]

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"