Incremental Refresh with Integers in Power BI

[@GoodLifeForFree]

Works great! Don't forget to convert the blank query to a function. Syntax: =(irparams as datetime) => Date.Year(irparams)*10000 + Date.Month(irparams)*100 + Date.Day(irparams)

[@damiendent7276]

Cheers Patrick, with your vid found where i was going wrong.. most appreciated.

[@RonnySadamHusen]

Love this. Thanks Patrick.

[@d3x0x]

Incremental refresh is awesome! Do you have a smart solution to handle incremental refresh for a table where the date will be in the future? In this case it's SQL.

[@suryarao503]

Thanks patrick for sharing this... Incremental refresh really makes me exited.. Cheers

[@mohitmanwani5298]

Perfect and clear explanation as always!! I would try to implement this in my Models..

[@SolutionsAbroad]

Thanks for sharing this Patrick, I've not used incremental refresh for my reports yet but I will save this video for that time!

[@GuyInACube]

Awesome. Thanks.

[@scfo]

Totally agree with you. Not used it but I can just imagine how valuable this technique may become it the future. Thanks gents. Do we really get all this value at no cost? Really appreciate the effort.

[@KurtBuhler]

Perfect. Thanks for answering those questions!

[@namitapatil7500]

Hi Patrick, thanks for the video. Your videos are always helpful!! I have a question if i have a json format data(cosmos db as a source and when i load the data in a desktop , it shows data type as Any for every field ) , how to do a query folding in this case or implement a incremental dataset refresh? I have tried several things but seems it is not working. please guide. Thanks

[@dpbartel]

Do you have an integer solution like what was shown in the desktop/service example for DataFlows as they require the use of a datetime type column for the Incremental refresh configuration?

[@noahswanson9940]

Would love if there were an answer to this problem. I am fighting it right now.

[@hovergamz]

Hi Pattrick, Can you make a video on how to pass a filter value to parameter to execute a stored procedure dynamically which has @startdate and @enddate

[@ahmedhashem908]

Thanks for sharing Patric.. Great .. 👊🏻

[@jimmydobbins7689]

Thanks for sharing. I can get this method to work with a dataset, but I can't figure out how to do it in a dataflow. Is that possible? It seems the entity must have a datetime column.

[@iambajpaisonam]

I came across this post to perform refresh operations through XMLA end point where you can overcome the contraint of 48 refreshes per day, but I am figuring it out to implement using postgre, can you please suggest the ways?

[@sureshparisa1427]

Hi Patrick, Is the incremental refresh will work with SQL Native queries?, we are facing issues and unable to see data gateway in service once we configure incremental refresh with SQL Native query. I did not find solution anywhere in google or youtube. If it is feasible please do a video on " Incremental refresh with SQL native queries".that would be helpful for so many guys in a cube😊

[@masalgemisi4514]

Hi Patrick, How do we do this with datamarts? Thanks

[@HARSHAUDAY11308]

Q: Hi patrick, how can we work incremental refresh with only integers like 829,830,831,832 like this.. I don't want use date time or datekey format. Purely am using integers only and that integers at any scenario not converted into datetime format

[@thenormalone9672]

Hi Patrick, what about having an epochtime, unix timestamp? How to handle that? The dax function duration is not foldable, I think.

[@markgriffiths9714]

Cheers Patrick, yes its does answer my question. So I have another for you, is there a way to use incremental refresh on the individual source tables which have been merged to form a fact table? I'm currently using dataflows with IR together with linked entities to generate my fact table in the service, but wondered if there was a way to do this in desktop without use of dataflows........

[@ncbshiva]

@Patrick, Can we do the same Incremental Refresh with Integers in Power BI Dataflows ? As dataflows only accepts datetime field for the incremental refresh to work in PBI Dataflows

[@GuyInACube]

I haven't tried, but my assumption is that it should work if you follow the steps in this video. You could copy and paste the code from desktop to dataflows also.

[@ncbshiva]

@@GuyInACube I did try by copy and pasting the code from desktop to dataflows, but in dataflows to enable incremental refresh you need to have Datetime field in your entity otherwise it will not work. I have already tried it, if you have any other solutions please let me know as it is required in one of my project.

[@bertscheer4829]

Awesome! Was just thinking about this. Wondering if this also could be used for (integer) surrogate keys not related to date(time). For example sequentially batchload id's

[@pawewrona9749]

Probably not, as you need to create 2 parameters with DateTime format, to have incremental refresh working.

[@sricharang9537]

This is perfect Patrick. Would like to know if this is possible even when using PowerBI data flows as the source to the dataset?

[@PrasanKumar]

Yes, if you have have premium / enhanced compute engine turned on. The data is then stored in SQL server DB tables instead of csv files on ADLS. Since the data is in DB, incremental refresh should be possible as the query folding should work.

[@BrianTX541]

Good stuff Patrick. Geaux Tigers!

[@mohitgarg360]

Have a query. I have 3 tables a,b,c. all three when linked by any column are forming many to many relationship. Is there a way to link them without many to many and still work ?

[@clnguye]

Brilliant!

[@dmitryvakhrushev81]

Why does the incremental refresh issue TWO identical queries for each day (e.g. refresh rows in the last 10 days)?

[@asheshshah2516]

Hey Patrick, Can you please show how to find no of days at row level in easiest way? @ Guy in a cube

[@deveshchouhan4204]

What if my query is not folding, will I be still able to do incremental refresh ?.. actually m using replicon connector for power bi desktop..and it's failing

[@GuyInACube]

You should watch this video on Query folder kzbin.info/www/bejne/h3apq5hjgcl_p9E. I am not sure what you mean replicon connector. Please elaborate

[@deveshchouhan4204]

It's one of data source ...power bi has native connector for this....we have to install this connector....www.replicon.com/help/using-microsoft-power-bi-to-analyze-your-replicon-data/

[@nedacil]

Thanks a lot

[@asheshshah2516]

Hey Patrick Awsum video I will save the video but how to do it?

[@GuyInACube]

Thanks. How to do what? Can you please elaborate?

[@asheshshah2516]

Guy in a Cube I got it

[@watervalle1585]

it works with text? for example 20201231

[@damionc]

I like incremental refresh, but not that it ends up deleting data from the beginning of your dataset eventually. I would have preferred to keep all the history data and just refresh new data going forward but keep all history. As it is now it will start to delete data from the beginning based on the refresh settings. Is there any way to "trick" the settings by saying you want to keep the last 100 years of data even though you only have 10 and so that way it never deletes anything? hmmmm........

[@Milhouse77BS]

Another function to get yyyyMMdd from Date is: Date.ToText(date,"yyyyMMdd") and could wrap with Number.From() to convert from String to Number.

[@GuyInACube]

Great point Donald. Nice. I will have to give that a try.

[@leonardomauro7894]

Amazing dude - hacking the hack. Bananas right?

[@damionc]

Wouldn't it have been easier to just change your keys into a datetime and be done with it?

[@eshas3930]

First to Comment. Can I get a like from Pattrick😊

[@discrate]

First you need to show some respect to him by writing his name correctly.

[@sagaon4]

I got lost!