Natives Queries or ODBC with Incremental Refresh in Power BI

Are you wanting to use Incremental Refresh in Power BI but need to use ODBC, native queries or something that doesn't fold properly? Adam shows you a solution for Power BI Desktop and Power BI dataflows!

Native Queries

Using Incremental Refresh with dataflows

Configure Increment Refresh for Datasets (includes DateKey function)


*******************

Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.


*******************
LET'S CONNECT!
*******************


***Gear***

#PowerBI #IncrementalRefresh #GuyInACube

[fernalication]

That's so cool!!!! That fold guy always in the middle. Nice workaround Adam thanks for sharing!

[victoralexandermoranteflor]

Thank a lot Adam!
I had applied the usual way to configure parameters for my View, and I was having a lot of problems. Whit your solutions, I have reduced 80% the time, and dont have problem in Services BI. I only have a issue whit the format of date, because I am in Latin America, but not more problems. Thank you so much.

[HariBI]

I was doing this already for my customer where we have complex logics. Added the range start and end in sql syntax itself and no more additional transformations. This was on Teradata.

[subanark]

If you are concerned about SQL injection, all you need to do is create a stored proc with the SQL query. Then navigate to that in the same way you navigate to a table. Finally, just put your RangeStart and RangeEnd into the function and invoke it.

[bertalanronai]

Yes, it blew my mind. I' try it out in 2 weeks.

[ZachRenwickData]

I've been thinking about how to best approach incremental refresh for sources that use ODBC for a few weeks now, so the timing of this video is great!

[coolblue]

I haven’t tried this but I will. Presumably this technique thwarts the dreaded Formula Firewall issue when injecting derived values into native queries.

[steefjanssen]

This is great! Aswell usefull for Web.Contents! Is there still a way to detect data changes based on a modified column? Maybe possible to pass this within a native query too?

[hosseinemami]

Great video, does date/type field needs to come from the source or we can simply change date type to date/time in query editor and follow the same steps? the reason I'm asking is because if I push date/time to my sorce it adds more Granular row to my data sets just because of the time part.

[jordankline]

This is great, but does it work with Detect Data Changes turned on? Dataflows automatically insert some query called "Canary" that looks like it takes the max value from a column - do those need to be translated into the native query as well?

[keithwalz]

Would appreciate any insights into the logic. I am using a record timestamp that is updated upon any change. Let's assume I am saving 20 years of records, updating the last year. If I have a record that was last updated in 2012, it will be stored in the 2012 partition. What happens if I update that record. It will get picked up in the refresh query, but will the logic also leave the old record in the 2012 partition?

[vaidehipimple]

I've tried this and it works great. Except for the data which has future data compared to the Ranges. Any idea on how the query should be modified for this scenario?

[kim]

Need help! Have several reports that are running stored procedures.... Can this work with sproc??

[xjkgofg]

Doing this in a Dataset Native Query that later is appended to another Native Query. I didn't need to deal with the datetime format, so just inserted RangeStart and RangeEnd in the 'query' section of the Advanced Editor but inside single quotes to make work. Syntax is okay, but the incremental refresh isn't happening in the service the results returning the hardcoded parameter range dates from the desktop. Wonder if it's because of the append query?

[guillermodebiase]

Hi Adam it amazing. question, after apply all these steps and run the refresh in my dataflow add a new line with Table.SelectRows(Source, each DateTime.From([FechaVisita]) >= RangeStart and DateTime.From([FechaVisita]) < RangeEnd)
Why?
My original query is
let
StartDate = DateKey(RangeStart),
EndDate = DateKey(RangeEnd),
query = "SELECT .... where FechaVisita >=" & Text.From(StartDate) & " and FechaVisita<" & Text.From(EndDate),
Source = MySQL.Database(server, database, [Query=query])

[AnilKumar-lngq]

Hi Adam, how to use incremental refresh using Amazon Athena using ODBC in the Power BI desktop report.

[Shawn-crep]

If my native SQL includes variables, CTEs, or temp tables then it will load the table preview in Power Query Editor, but will have a syntax error when loading the data to the model.

[dipsikhadas]

Adam could you share the link to the documentation you referenced in the video

[drScorpon]

Your channel is incredibly useful. One question: does this work also for "Premium per user" license?

[trevormay]

Is the integer key step necessary if you have a DateTime column?