Incremental Refresh YYYYMM datatype INT/STR

[apollo89]

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!

[v-yuta-msft]

Hi apollo89 ,

You should click editor query-> advanced editor and add this custom function there.

Regards,

Jimmy Tao

[apollo89]

Hi Jimmy,

Thank you for your reply.

When you say custom function in advanced editor, what do you mean? I don't see an option for a custom function there.

I tried to do following:

 which gave me

 

I also tried another way as mentioned in https://community.powerbi.com/t5/Desktop/Incremental-refresh-with-ID-YYYYMMDD/td-p/437844

which gives me a blank table

I have defined the parameters as mentioned in the documentation and the dates in the table are part of the parameter range.

Anything I am doing wrong?

Thanks for your help!

[v-yuta-msft]

Hi apollo89,

You can refer to these blogs about how to create custom functions.

https://blogs.msdn.microsoft.com/mvpawardprogram/2013/08/19/creating-power-query-functions/

http://radacad.com/writing-custom-functions-in-power-query-m

Regards,

Jimmy Tao

[apollo89 · Accepted solution]

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!