Append large data files - best practice?

[bilingual]

Hi, i have used Power Query in order to combine historical data in our organization. 

The problem is the only form of export from SAP webi is either excel or csv format. 

As every months has about 125.000 rows and 15 columns, i have made a workaround by creating Excel files per Quarter and then combined them with Append and then created relationships with the Append data to other tables.

However it is still very heavy to use as data in the Append has reached about 8 mio rows and i am continually adding data. 

With my limit in the export opportunities can anybody suggest a better solution?

[Anonymous]

If these csv files are all in the same folder, can't you just use the folder connector so that all of your new files are added to the dataset upon refresh?

--Nate

[ppm1]

You can use incremental refresh with files, if you can parse a date value from the filenames. Please see this video.

https://www.youtube.com/watch?v=IVMdg16yBKE

Pat

[Maha1]

Hi @bilingual , @johannesDE , @v-alq-msft , @PC2790 ,

This was the best solution!

https://youtu.be/9WLZ9jfoXwA

[johannesDE]

I am also interested in finding the best practice in a case like this.

So far, I have split a large fact table by year, turned off refresh for prior years and appended the data in PowerQuery.

I am not sure if this will improve performance.

[v-alq-msft]

Hi, @bilingual 

I'd like to suggest you try the following data reduction techniques:

• Remove unnecessary columns
• Remove unnecessary rows
• Group by and summarize
• Optimize column data types
• Preference for custom columns
• Disable Power Query query load
• Disable auto date/time
• Switch to Mixed mode

For further information, you may refer to the following document.

Data reduction techniques for Import modeling 

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PC2790]

Have you considered using Direct Query and incremental refresh to load the new data keeping the old data intact? 

[bilingual]

Unfortunately it does not work with csv or Excel files.