Forum Discussion

DennisSchlein's avatar
DennisSchlein
Helper III
2 years ago
Solved

Incremental refresh designer - error

Hi 

I'm trying to setup incremental refresh in a report.
This is connected to a azure sql.

let
startDate = DateKey(RangeStart),
endDate = DateKey(RangeEnd),
    Source = OleDb.DataSource("provider=MSOLEDBSQL.1;initial catalog=webhook_testing;data source=XXXX;connect retry count=10", 
[Query="SELECT
#(lf)                   
d.id,
#(lf)                    
JSON_VALUE(d.additional_info, '$.""Cmr number""') AS [Cmr number]
,#(lf)                    
JSON_VALUE(d.additional_info, '$.""Company registration number""') AS [Company registration number]
,#(lf)                    
LEFT(d.created_date, 19) AS Created_date,
#(lf)                    d.additional_info AS ADDITIONAL_INFO,
#(lf)                    d.shipment_id,
#(lf)                    d.tracking_number,
#(lf)                    d.shipment_reference,
#(lf)                    d.carrier AS carrier_name,
#(lf)                    d.carrier_reference,
#(lf)                    d.client_name,
#(lf)                    d.client_id,
#(lf)                    d.shipping_service,
#(lf)                    d.tags,
#(lf)                    d.status,
#(lf)                    d.sender_country_code,
#(lf)                    d.recipient_postal_code,
#(lf)                    d.recipient_country_code
#(lf)                FROM
#(lf)                    dbo.data d
#(lf)                WHERE
#(lf)                    d.created_date >= " & Text.From(startDate)]),
    
                    
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Created_date", type datetime}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [Created_date] > @RangeStart)
in
    #"Filtered Rows"



DataSource.Error: OLE DB: Operand type clash: datetime2 is incompatible with int
Details:
DataSourceKind=OleDb
DataSourcePath=connect retry count=10;data source=ubbi.database.windows.net;initial catalog=webhook_testing;provider=MSOLEDBSQL.1
Message=Operand type clash: datetime2 is incompatible with int
ErrorCode=-2147217913

What am I doing wrong?

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi DennisSchlein ,

    It looks as if the cause of your refresh error is happening on the data type 'datetime2':

    Please check the data types of the columns in your database table. Ensure that the data types of the columns match the data types of the parameters used in the query.
    Both the parameters data type and the filtered data column must be Date/Time data type and the format must be the same.

    You can learn more about Incremental refresh and maybe you can find solution here:
    Configure incremental refresh and real-time data for Power BI datasets - Power BI | Microsoft Learn

    Best Regards,

    Dino Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi DennisSchlein ,

    It looks as if the cause of your refresh error is happening on the data type 'datetime2':

    Please check the data types of the columns in your database table. Ensure that the data types of the columns match the data types of the parameters used in the query.
    Both the parameters data type and the filtered data column must be Date/Time data type and the format must be the same.

    You can learn more about Incremental refresh and maybe you can find solution here:
    Configure incremental refresh and real-time data for Power BI datasets - Power BI | Microsoft Learn

    Best Regards,

    Dino Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.