Forum Discussion

Mohan128256's avatar
Mohan128256
Helper IV
1 year ago
Solved

Incremental Load with SQL & Network located excel files

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.

 

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    1 year ago

    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's avatar
    Kedar_Pande
    1 year ago

    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

     

     

8 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    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's avatar
      Mohan128256
      Helper IV
      1 year ago

      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.

  • amitchandak's avatar
    amitchandak
    Super User
    1 year ago

    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

     

  • Mohan128256's avatar
    Mohan128256
    Helper IV
    1 year ago

    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's avatar
      amitchandak
      Super User
      1 year ago

      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's avatar
        Mohan128256
        Helper IV
        1 year ago

        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.