Forum Discussion
DAX: 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
I can see you are doing things the wrong way all along. If you have a model where tables are in an Import mode (and I assume you do), then all the tables are imported through Power Query. You should do all our ETL in Power Query, not in DAX. UNION on 2 fact tables in DAX? Very bad idea. Please do all your data preparation in Power Query. This is the right and correct way. Power Query is a data mashup engine for a reason... DAX is for Data Analysis (Data Analysis eXpressions). Please note that if you don't program your PBI correctly from the start, you'll be having huge problems later down the line. Trust me: You don't want to do things incorrectly.
OK, 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...
The fact is that we have 500 files and each has 10-15 thousand lines. It takes us 3 hours. I say this not because I'm making it up, but as it is in practice.
10 thousand is not monstrous files, monstrous is 800 thousand and millions.
Or even on a few tabs like to do.I'll look at your link about incremental updates - but as far as I understand it requires a field with the date of addition - we do not have it.
More precisely, there is but it does not match the current one.Therefore, it was decided to do it in the only possible way.