I'm using dataflows in our premium embedded workspace to pull various files/tables to report in Power BI and make them available across multiple datasets. This works great for all the dataflows I've built so far. Refreshes are taking 5-10 minutes for dataflows in the 500 mb to 2 gb range with sources being sales data in ADLS Gen2 data lake CSV and txt files, along with the Spark tables in our Azure Synapse workspace. However, I've tried to create a dataflow based on an "Archive" structure of hourly files in our data lake. Dataflows based on files in our data lake of one file per day are running fine (15 minutes to refresh the dataflow). This Archive structure has 24 files per day, many of which are empty. This dataflow is taking over 2 hours to refresh. I should note that the actual files for the daily single file and the archive files are VERY similar in data (sales type data of 5-10 string columns and 5-10 numeric columns).
The lake file path structure looks like this:
Customer / marketplace / Archive or snapshot / Year/month/day (depending on archive or snapshot)
I've looked at our Tenant Metrics in the Azure Portal, and the 2 hour refresh finishes and has the same memory and QPU load whether we're on tier A2 or A4. I'm at a loss, and assume its something to do with the backend of Power Query and how it reads files. I'm also worried that as our data lake size increases with 24 new files every day for each customer, the dataflows refresh time will skyrocket and make it unusable. Any ideas?
