How to hasten data load after changes.

I'm not saying you should append them. That would be a manual process each time there is a new file but instead, strike a balance between the two approaches. The current combine feature doesn't do much other than letting you combine the files without much coding. I would do these:

• Process everything in Desktop
  • partition the files into different folders which means one combine will have lesser files to work on
  • append each combine into one
  • Or pre-process historical/static/files that are unlikely to change in Dataflow
    • Partition the files same as above, each partition having its own query
      • Save the Dataflow and refresh it
      • Connect to the Dataflow from the desktop
      • Append the Dataflow tables together with the local query (for current file/s)
      • Make sure each query both from the Dataflow and local in the Desktp have the same transformations prior to appending the. Apply additional transformations after appending if needed.

        Data from Dataflows are pre-processed so transformations are not applied to the raw data but to the result of the Dataflow instead. This should take most of the legwork from the Desktop. Dataflows have their own separate refresh and are not triggered when the semantic model itself is refreshed. Refresh the dataflows if the files within the folder the Dataflows are connected have been updated. Note: Dataflow requires a pro workspace.se the code below to combine them as using the combine feature in Dataflow will likely result to a compute entity which requires premium. Modify the path, etc

         

         let
        Source = SharePoint.Files(spsite, [ApiVersion = 15]),
        #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = spfolderpath)),
        #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Extension] = ".csv"),
        #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Name", "Content"}),
        #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Connect to csv", each Table.PromoteHeaders( Csv.Document([Content],[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.Csv]) ))
        in
        #"Added Custom" 

        

You're going to have some slowness with 8 million rows from SharePoint. SharePoint limits the speed you can pull data with for stability reasons - it's not a data warehouse. 🙂

 

Make sure to remove any columns you're not using as your first step in the query, and keep the number of transformations to a minimum - that can help a bit. You might also consider putting the initial data load into a dataflow, then connecting to that doing your transforms on the desktop side to "split" the processing load. The connection to dataflows is very fast, so it can sometimes help.