cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
apollo89 Helper I
Helper I

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
apollo89 Helper I
Helper I

Re: 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
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!

View solution in original post

4 REPLIES 4
Community Support
Community Support

Re: Incremental Refresh YYYYMM datatype INT/STR

Hi apollo89 ,

 

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

 

Regards,

Jimmy Tao

apollo89 Helper I
Helper I

Re: Incremental Refresh YYYYMM datatype INT/STR

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:

 

Capture.PNG

 

 

 which gave me

 

Capture.PNG 

 

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

 

Capture.PNG

 

which gives me a blank table

 

Capture.PNG

 

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!

Community Support
Community Support

Re: Incremental Refresh YYYYMM datatype INT/STR

apollo89 Helper I
Helper I

Re: 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
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!

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors