Forum Discussion
Helper IDAX: cyclic dependency detected
Following up on my last post... Here's how to make PBI work with incremental refresh when using files: Chris Webb's BI Blog: Keep The Existing Data In Your Power BI Dataset And Add New Data To It Using Incremental Refresh Chris Webb's BI Blog (crossjoin.co.uk)
And here's the article that Chris refers to at the beginning of his article: Incremental refresh for files in a Folder or SharePoint - Power BI — Powered Solutions
Helper IOK, how do you load data from 500 Excel files into Power Query with the same format? You will update the report for 3 hours when a new file appears or a single file in a folder is updated.
We have a new Excel file in the folder and the division into several tables by year is made so that the old data is not updated, but only the data for 2021 (current) is updated.
When analyzing data, we work with a common table (including all periods). If you have a better solution, tell me - how can I do this task correctly?
I've created 500 Excel files in a folder on my PC. In each there is a table with 20 columns (a mix of integers, floats and text) and 365 rows. I used the Folder connector in Power Query to load this data, automatically consolidate into one table and then load into PBI Desktop. The process of fully refreshing all data takes not more than about 10 seconds.
So, the question is: How many rows do you have in each of the files that you are worried about the refresh time? I'm not sure how to do an incremental refresh when loading data from Excel files and if it's possible at all. I'll have to find out. But if you don't have monstrous Excel files with tens of thousands of rows, then I can't see why you couldn't do what I've done...