Power Query no error but I get an Error on close and apply post refresh

[FKleinclaus]

I am trying to implement incremental refresh on my biggest data source and it's an OData API in an application I develop. 

I tried to add the filter after the API call but it was still downloading the whole data and filtering it afterwards. 

I tried to add the filters in my query in order to filter directly in my call. But PowerBi refused to refresh my data because my query was dynamic. 

I looked a bit on google and I found another way to build my query. 

let
    Source = OData.Feed(ApiRoutePath&"odata/v1/store/fact_table?filter=UpdateDate ge @rangeStart and UpdateDate lt @rangeEnd &$select=FactId,Datatype,YearMonth,ScoreId,Admin1GuidForMap,Admin2GuidForMap,MaxWeight,ComputedValue,ComputedWeight,ComputedColor,Email,UserId,RoleId,RoleName,FkGeoPlaceId,OfficialName,GeolevelId,GeolevelName,Admin0,Admin0Id,Admin1,Admin1Id,Admin2,Admin2Id,IndicatorValue,Numerator,Denominator,IndicatorCode,IndicatorId,IndicatorName,ActualWeight,ScoreDetailColor,RLS,ScoreOrEvalAdmin0Id,EAction,EvalId,TotalDetails,TotalDetailsFulfilled,Completeness,StaffsTextColor,OneStaffTextColor,RegionalTextColor,Performance, UpdateDate",
       null, [
                Headers=[Authorization="Bearer " & GetTokenV2()],
                Query = [
                        #"@rangeStart" = Date.ToText(DateTime.Date(RangeStart),"YYYY-MM-DD"),
                        #"@rangeEnd" = Date.ToText(DateTime.Date(RangeEnd),"YYYY-MM-DD")
                ]
            ]
    ),
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Email", "Email - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Email - Copy", Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv), {"Email - Copy.1", "Email - Copy.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Email - Copy.1", type text}, {"Email - Copy.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Email - Copy.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Email - Copy.1", "EmailWithoutProvider"}}),
    #"Type modifié" = Table.TransformColumnTypes(#"Renamed Columns",{{"UpdateDate", type datetime}}),
    #"Lignes filtrées" = Table.SelectRows(#"Type modifié", each [UpdateDate] >= RangeStart and [UpdateDate] < RangeEnd)
in
    #"Lignes filtrées"

This query is working in the power query editor but when I close and apply I get this error. 

I feel a bit at a dead end now. Maybe is there an easier way to use an incremental refresh for Odata. Maybe I am really near to the solution but I felt like asking here to feel a little bit less alone in my situation.

EDIT 1 : 
When I launch the diagnostic for my query I get the query which is used : 

https://afro-af-uat.novel-t.ch/odata/v1/store/fact_table?filter=UpdateDate ge %40rangeStart and UpdateDate lt %40rangeEnd &%24select=FactId%2CDatatype%2CYearMonth%2CScoreId%2CAdmin1GuidForMap%2CAdmin2GuidForMap%2CMaxWeight%2CComputedValue%2CComputedWeight%2CComputedColor%2CEmail%2CUserId%2CRoleId%2CRoleName%2CFkGeoPlaceId%2COfficialName%2CGeolevelId%2CGeolevelName%2CAdmin0%2CAdmin0Id%2CAdmin1%2CAdmin1Id%2CAdmin2%2CAdmin2Id%2CIndicatorValue%2CNumerator%2CDenominator%2CIndicatorCode%2CIndicatorId%2CIndicatorName%2CActualWeight%2CScoreDetailColor%2CRLS%2CScoreOrEvalAdmin0Id%2CEAction%2CEvalId%2CTotalDetails%2CTotalDetailsFulfilled%2CCompleteness%2CStaffsTextColor%2COneStaffTextColor%2CRegionalTextColor%2CPerformance%2C UpdateDate&%40rangeStart=2022-01-01&%40rangeEnd=2023-01-26

And when I click on it requires authentification but it is working

[FKleinclaus]

I managed to avoid the error with this: 

let
    Source = OData.Feed(ApiRoutePath&"odata/v1/store/fact_table?filter=@filter &$select=FactId,Datatype,YearMonth,ScoreId,Admin1GuidForMap,Admin2GuidForMap,MaxWeight,ComputedValue,ComputedWeight,ComputedColor,Email,UserId,RoleId,RoleName,FkGeoPlaceId,OfficialName,GeolevelId,GeolevelName,Admin0,Admin0Id,Admin1,Admin1Id,Admin2,Admin2Id,IndicatorValue,Numerator,Denominator,IndicatorCode,IndicatorId,IndicatorName,ActualWeight,ScoreDetailColor,RLS,ScoreOrEvalAdmin0Id,EAction,EvalId,TotalDetails,TotalDetailsFulfilled,Completeness,StaffsTextColor,OneStaffTextColor,RegionalTextColor,Performance, UpdateDate",
       null, [
                Headers=[Authorization="Bearer " & GetTokenV2()],
                Query = [
                        #"@filter" =  "UpdateDate ge " & Date.ToText(DateTime.Date(RangeStart),"YYYY-MM-DD") &" and UpdateDate lt "& @Date.ToText(DateTime.Date(RangeEnd),"YYYY-MM-DD")
                ]
            ]
    ),
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Email", "Email - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Email - Copy", Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv), {"Email - Copy.1", "Email - Copy.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Email - Copy.1", type text}, {"Email - Copy.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Email - Copy.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Email - Copy.1", "EmailWithoutProvider"}}),
    #"Type modifié" = Table.TransformColumnTypes(#"Renamed Columns",{{"UpdateDate", type datetime}}),
    #"Lignes filtrées" = Table.SelectRows(#"Type modifié", each [UpdateDate] >= RangeStart and [UpdateDate] < RangeEnd)
in
    #"Lignes filtrées"

PowerBi service accepts to refresh. I will need to check if the refresh are really incremental now.

[ppm1]

Two points that may help you get unstuck.

1.  To leverage incremental refresh, since this source/query is probably not "foldable", you will need to work RangeStart and RangeEnd into your API call (convert them to text in correct format and concatenate it into the query).

2. You will need to use the RelativePath term in Web.Contents to avoid the dynamic source error.

Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query An...

Pat

[FKleinclaus]

Hello ppm1 and thank you very much for your answer! 

#"@rangeStart" = Date.ToText(DateTime.Date(RangeStart),"YYYY-MM-DD"),
#"@rangeEnd" = Date.ToText(DateTime.Date(RangeEnd),"YYYY-MM-DD")

I am using the RangeStart and RangeEnd parameters here. 
Your second point is related to Web.Contents but I am using Odata.Feed because my API returns only a certain amount of rows and I need odata to get all rows with the skip. Well, that's why I set up the Odata.Feed() in the first place but I will try. 

It doesn't explain why it works in power query 😕

Anyway, Thank you again for your answer!