Help for incremental data loading from FTP source

[cwickram]

Currently I have add FTP folder (containing multiple excel files) as my power BI data source under the  combine and load option. When running it will combine all the excel files and load to Power BI.

But I am looking to set above for incremental data loading. What I need to do is only load the newly add excel files from last data loading. Finally my Power BI application should have (all last loading data + newly added excel data)

When I search on the internet I saw there are many for direct sources. But I couldn’t find a solution for the non-direct sources such as FTP/excel.

Do you have any idea on above? Could you please help?

[Jimmy801 · Accepted solution]

Hello @cwickram 

I cannot tell you anything about Power BI Service. What i can tell you is that loading 2000 files is no issues. It will take some time to read them all.

I would understand this limit as 1 Gb-dataset, not 1 Gbyte data as raw data in your files. In case you have to transform your data, that your are not reaching this limit at all 🙂

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[gayatri97]

can we connect Power BI to ftp Server Dirctly

[Jimmy801]

Hello @cwickram 

I'm afraid to tell you that in Power Query there is no possibility of incremental refresh. You need to look out for other Power apps

BR

Jimmy

[cwickram]

Hi Jimmy801,

Today I went through some articals and identified the difficulties when using incremental refresh for non direct quesry sources such as flat files.

So I decided to go ahead with folder sharing as the source for Power BI. It means add  one folder as the source file location (Under the folder option) and trying to combine and load all the files on relavant folder location.

Is there any limitation on above kind of folder sharing and combine/load? Is there any risk on 

1.Number of files - We have around 2000 files

2.Size

3.Performances

Could you please check on above and give your feedback.?

Or what is your recomandations to use as a solution for above kind of problem?

Thanks again for your help.

Cwickram

[Icey]

Hi @cwickram ,

There is no limitations on numbers of files. But there are some other limitations that need to be noted:

For all data sources used in the Power BI service, consider the following limitations. There are other limitations that apply to specific features, but the following list applies to the full Power BI service:

• Dataset size limit - There's a 1-GB limit for datasets stored in Shared capacities in the Power BI service. If you need larger datasets, you can use Power BI Premium.

• Distinct values in a column - When caching data in a Power BI dataset (sometimes called 'Import' mode), there is a 1,999,999,997 limit on the number of distinct values that can be stored in a column.

• Row limit - When using DirectQuery, Power BI imposes a limit on the query results that are sent to your underlying data source. If the query sent to the data source returns more than one million rows, you see an error and the query fails. Your underlying data can still contain more than one million rows. You're unlikely to run into this limit as most reports aggregate the data into smaller sets of results.

• Column limit - The maximum number of columns allowed in a dataset, across all tables in the dataset, is 16,000 columns. This limit applies to the Power BI service and to datasets used in Power BI Desktop. Power BI tracks the number of columns and tables in the dataset in this way, which means the maximum number of columns is 16,000 minus one for each table in the dataset.

---

Reference: Data sources for the Power BI service # Considerations and limitations

In addition, for performance, please refer to these documents:

Optimization guide for Power BI;

Power BI Best Practices;

Power BI Performance Optimization Tips;

HOW TO IMPROVE POWER BI PERFORMANCE - PART I.

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[cwickram]

Hi Icey and Jimmy801,

Thank you for the support and comments.

To clarify more I need to know below.

1)Is this 1GB limit applicabale when publishing PBIX from Power BI Desktop to Power BI service? (Power BI Desktop to relavant workspace). 

If so what is the size limitation for data refresh? I am using Power BI pro.

2)Does this 1GB limitation mean we can only have maximum 1GB size data refresh through PowerBI service? (I mean the data refresh max size is 1 GB?) It means we need to limit the data refresh size to 1GB max limit?

[Jimmy801 · Accepted solution]

Hello @cwickram 

I cannot tell you anything about Power BI Service. What i can tell you is that loading 2000 files is no issues. It will take some time to read them all.

I would understand this limit as 1 Gb-dataset, not 1 Gbyte data as raw data in your files. In case you have to transform your data, that your are not reaching this limit at all 🙂

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[cwickram · Accepted solution]

Thank you Jimmy801 and Icey for the support given.!

[cwickram]

Artical I refer to get folder share and combine and load.

https://www.poweredsolutions.co/2019/04/23/combine-or-append-data-combining-excel-files/