Forum Discussion
Incremental Refresh YYYYMM datatype INT/STR
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
SourceIn 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!
Hi apollo89 ,
You should click editor query-> advanced editor and add this custom function there.
Regards,
Jimmy Tao
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!
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
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
SourceIn 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!