Incremental refresh with left join

[joshua1990]

Hello everyone!

I am trying to add some ERP Data through sql into my data set.

I have one Order Master, that cointains all dates, value etc per order nr:

Order	Created	Started	Finished	Quantity	Value
1	01.01.2019	01.01.2019	01.02.2019	50	5
2	01.01.2019	01.01.2019	01.03.2019	45	5

And I have a Item Ledger, that contains all booking types per order nr:

Order	Booking	Value	Date
1	Export	5	02.02.2019
1	Entry	5	01.02.2019

Now I would like to include the Item ledger with an incremental refresh into my dataset.

But I would like to add just data from Item Ledger for orders that are also in the Order master listed.

This would be a simple left join in sql, right?

Is this also possible when adding an incremental refresh in the end?

[mwegener]

Hi @joshua1990 ,

did you see my blog post on the subject?

Incremental Refresh with Subqueries 

https://community.powerbi.com/t5/Community-Blog/Incremental-Refresh-with-Subqueries/ba-p/1003337

[Greg_Deckler]

@joshua1990 - Well, you can do a left join in Power Query Editor using a Merge query. Not sure I 100% understand the dilemma with incremental refresh and such.

[joshua1990]

I mean, from a performance point of view I would do the join within a individual sql statement, right?

But that would also mean I can't add an incremental refresh in the end, right?

I guess I have to do all operations in Power Query.

[Greg_Deckler]

@joshua1990 - If you want to only load the main table once and do incremental on the ledger, then yes I would think you would need to do that in Power Query and then the Merge there. Alternatively, if you did the join in SQL as a view perhaps, could you do an incremental against that view? I'm not sure I see why not exactly but I may not be thinking it through 100%

[joshua1990]

Thank you so much for your support @Greg_Deckler ! 
I have just read only rights in our data warehouse. 
If I join the two tables in a sql I am working on a non foldable query, right? 
that would mean I can't add an incremental refresh. 
my question is: If I want to add an incremental refresh have I to use M to join two tables? 
Or can I join two tables within a SQL select and then add an incremental refresh?

[edhans]

@joshua1990 - do NOT use the Advanced Query feature in Power BI where you can enter a SQL statement. It prohibits all further query folding and Incremental Refresh will not work. It seems that might be what you are doing, but you haven't specifically said.

Either create a View in SQL Server and connect Power BI to that, or do all of it in the Power query editor itself. It will fold just fine.

If this doesn't help, please post back with what you are specifically doing all the way through.

[Greg_Deckler]

@joshua1990 I do not believe that joining two tables in SQL necessarily creates a non-foldable query. Give it a shot and see if you can see the native query. It's worth the 10 minutes it would take to write the join SQL syntax.

https://docs.microsoft.com/en-us/power-query/power-query-folding

https://docs.microsoft.com/en-us/power-bi/guidance/power-query-folding