Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Incremental Refresh - Trouble applying

Hello,

 

I'm trying to setup incremental on one of my fact tables:

 

I have the following text on my Power query:

 

 

 

 

let
    startDate = DateKey(RangeStart),
    endDate = DateKey(RangeEnd),
    query = Text.Format("
    -- Table: fPageViews
    USE JUMIA_CFG
    EXEC [dbo].[sp_select_union_all_countries] '
    -- Final
    SELECT
    SUBSTRING(''_XX_'',2,2)                                              [COUNTRY_CODE]
    ,wi.SK_DATE                                                          [SK_DATE]
    ,CAST(CAST(wi.SK_DATE AS VARCHAR(8) ) AS DATE)                       [DATE]
    ,CONCAT(SUBSTRING(''_XX_'',2,2),''_'',wi.DD_PRODUCT_CONFIG_ID)       [COD_CATALOG_CONFIG]
    ,CASE WHEN SK_SHIPPING_TYPE IN (3,8,13,16) THEN 1 ELSE 0 END         [FLG_JE]
    ,SUM(wi.MTR_QTY_PRODUCT_PAGEVIEWS)                                   [PAGE_VIEWS]
    ,SUM(wi.MTR_QTY_PRODUCT_ADDED_TO_CART)                               [ADDED_TO_CART]

    FROM [AIG_AAA_XX_DW].[dbo].[FCT_WEB_INDICATORS] wi
    WHERE  wi.SK_DATE >= '{0}' AND wi.SK_DATE <= '{1}'
    GROUP BY
     wi.SK_DATE
    ,wi.DD_PRODUCT_CONFIG_ID
    ,CASE WHEN SK_SHIPPING_TYPE IN (3,8,13,16) THEN 1 ELSE 0 END
    ",startDate,endDate),
    Source = Sql.Database("mall-sql.aaa.local", "AIG_AAA_UG_DW", [CommandTimeout = #duration(0, 2, 0, 0), Query = query]),
    change_column_type = Table.TransformColumnTypes(Source, {{"SK_DATE", type text}, {"DATE", type datetime}}),
    #"fPageViews-44415445-autogenerated_for_incremental_refresh" = change_column_type
in
    #"fPageViews-44415445-autogenerated_for_incremental_refresh"

 

 

 

 

 

This query is throwing me this error:

Any idea on what might be the issue? SK_DATE is a column with the date as integer -> 20210101, 20210102 and so on

Thanks

Diego

 

Need Help
Show and Tell

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    I tried to reproduced it but failed. It works fine on my device.

    Testing code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjI0MrJQ0gEzdQ2NdI0sFAzNrYwNrAwMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SK_DATE = _t, DATE = _t]),
    change_column_type= Table.TransformColumnTypes(Source,{{"SK_DATE",type text}, {"DATE", type datetime}}),
 #"fPageViews-44415445-autogenerated_for_incremental_refresh" = change_column_type
in
    #"fPageViews-44415445-autogenerated_for_incremental_refresh"

     

    Perhaps you should try to get the source table first and click Detect Data Type in Power Query Editor.

    let
    startDate = DateKey(RangeStart),
    endDate = DateKey(RangeEnd),
    query = Text.Format("
    -- Table: fPageViews
    USE JUMIA_CFG
    EXEC [dbo].[sp_select_union_all_countries] '
    -- Final
    SELECT
    SUBSTRING(''_XX_'',2,2)                                              [COUNTRY_CODE]
    ,wi.SK_DATE                                                          [SK_DATE]
    ,CAST(CAST(wi.SK_DATE AS VARCHAR(8) ) AS DATE)                       [DATE]
    ,CONCAT(SUBSTRING(''_XX_'',2,2),''_'',wi.DD_PRODUCT_CONFIG_ID)       [COD_CATALOG_CONFIG]
    ,CASE WHEN SK_SHIPPING_TYPE IN (3,8,13,16) THEN 1 ELSE 0 END         [FLG_JE]
    ,SUM(wi.MTR_QTY_PRODUCT_PAGEVIEWS)                                   [PAGE_VIEWS]
    ,SUM(wi.MTR_QTY_PRODUCT_ADDED_TO_CART)                               [ADDED_TO_CART]

    FROM [AIG_AAA_XX_DW].[dbo].[FCT_WEB_INDICATORS] wi
    WHERE  wi.SK_DATE >= '{0}' AND wi.SK_DATE <= '{1}'
    GROUP BY
     wi.SK_DATE
    ,wi.DD_PRODUCT_CONFIG_ID
    ,CASE WHEN SK_SHIPPING_TYPE IN (3,8,13,16) THEN 1 ELSE 0 END
    ",startDate,endDate),
    Source = Sql.Database("mall-sql.aaa.local", "AIG_AAA_UG_DW", [CommandTimeout = #duration(0, 2, 0, 0), Query = query]),
    
in
    Source

    Add or change data types (Power Query) - Microsoft Support

     

    Best Regards,

    Stephen Tao

     

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