Forum Discussion

apollo89's avatar
apollo89
Helper II
7 years ago
Solved

Incremental Refresh YYYYMM datatype INT/STR

Hi All,

 

I am configuring incremental refresh according to documentation https://docs.microsoft.com/en-us/power-bi/service-premium-incremental-refresh

The difference here is that our databases have the dates in the formats of YYYYMM/YYYYMMM as integer or string data types.

The hint in the document mentions to place this query in the filter step to convert to an integer: (x as datetime) => Date.Year(x)*10000 + Date.Month(x)*100 + Date.Day(x)

I am confused as to where this filter step is as once I create the parameters I only see the date/time I provided in the current value.

 

Thanks!

 

Need Help
  • apollo89's avatar
    apollo89
    7 years ago

    Hi Jimmy,

     

    Thank you for your response.

    I did manage to create the function.

    I'll describe my solution in case it helps anyone.

    My data had date related fields in the format of YYYYMM and YYYYMMM in string format.

    To convert the RangeStart and RangeEnd parameters into string,

    Edit Queries > New Source > Blank Query

     

    let
    Source = (x as datetime) => Text.From(Date.Year(x)) & Text.PadStart(Text.From(Date.Month(x)),2,"0")
    in
    Source

     

    In case data format is YYYYMMM, change the 2 to 3.

     

    Since I was using custom SQL, I called the function as a filter in the WHERE clause.

     

    FISCAL_MONTH >= '"&INCREFFUNC(RangeStart)&"' and FISCAL_MONTH  < '"&INCREFFUNC(RangeEnd)&"'

     

    where INCREFFUNC is the name of the function I created.

     

    After doing these steps, incremental refresh works!

     

    Hope this helps!

4 Replies