Incremental Load with SQL & Network located excel files

[Mohan128256]

Hi All,

I am trying to implement Incremental refresh on tables where A table is coming from SQL source and B is a excel table which is placed over a network location.

B excel file has 3 different sheets which i am appending together.

Then, A(SQL Table) appending with B(excel table with 3 sheets appended).

I would like to implement incremental refresh on these data of tables.

So far, I have made the IR implementation on SQL table and then appending excel file table.

it is getting loaded to power bi desktop but when i refresh it in service, unfortunately it is consuming the whole resources of workspace capacity.

please advice on the right approch for this.

Thanks,

Mohan V.

[amitchandak · Accepted solution]

@Mohan128256 , If you are a power BI user, not fabric.

Power BI With DataFlow

Append All the Excel in one data flow

Then you can load the SQL server in Power BI File and then append it with table that you load from SQL server 

The same can be done in one dataflow or power Query (Desktop) too

In the case of Fabric, I can load data in One Dataflow Gen2. If needed I can append data in SQL or pySpark based on destination.

Hope this can help

[Kedar_Pande · Accepted solution]

@Mohan128256 

1. Create a dataflow for your Excel data
   Combine all 3 sheets in the dataflow
   Set up scheduled refresh for the dataflow
2. Implement incremental refresh on SQL table directly
   Use RangeStart and RangeEnd parameters
3. Load data from dataflow (Excel data)
   Load incrementally refreshed SQL data
   Append these two sources

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Mohan128256]

@Kedar_Pande 

thanks for the reply.

Please correct if i understood it right or not.

1. Power BI Service Dataflow -
   1. Create a dataflow for your Excel data
      Combine all 3 sheets in the dataflow
      Set up scheduled refresh for the dataflow
2. Power BI Desktop -
   1. Implement incremental refresh on SQL table directly
      Use RangeStart and RangeEnd parameters
3. Power BI Desktop -
   1. Load data from dataflow (Excel data)
      Load incrementally refreshed SQL data
      Append these two sources

Thanks,

Mohan V.

[Anonymous]

Hi @Mohan128256 

Did amitchandak 's and Kedar_Pande 's methods solve your problem? If yes, could you please mark them as solutions? This will help more users who are facing the same or similar difficulties. Thank you!

If you still have questions, please feel free to ask me.

Best Regards,
Yulia Xu

[Kedar_Pande]

@Mohan128256  That's correct

[Mohan128256]

@amitchandak thanks for finding the time to reply.. Been a fan of your content.

Can i be able to do it by having these excel files appending in dataflow and then in power bi model append the dataflow to sql table data together?

Will that work? any thoughts.?

Thanks,

Mohan V.

[amitchandak · Accepted solution]

@Mohan128256 , If you are a power BI user, not fabric.

Power BI With DataFlow

Append All the Excel in one data flow

Then you can load the SQL server in Power BI File and then append it with table that you load from SQL server 

The same can be done in one dataflow or power Query (Desktop) too

In the case of Fabric, I can load data in One Dataflow Gen2. If needed I can append data in SQL or pySpark based on destination.

Hope this can help

[Mohan128256]

@amitchandak  what i understood from your reply is.

1. Create dataflow with excel file which has 3 sheets, append it in dataflow

2. Load SQL table in power bi desktop, implement incremental refresh

3. Using Power Query append DataFlow table to SQL table together.

Please correct if it is not the approach you are suggesting.

Thanks,

Mohan V.

[amitchandak]

@Mohan128256 , You can set up incremental for SQL server based on dates. But I doubt you can do that for Excel.

Also, you should make sure you refresh data in service, not on desktop

https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview

Power BI Incremental Refresh Pro; Premium Deployment pipeline - https://youtu.be/nIxTRdeCYSE

Check Suggestions from GuyinCube to reduce the load
https://www.youtube.com/watch?v=qZOEDBedATA