Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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!
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |