Forum Discussion
PowerBI Create a new Dataflow from 2 separate DataFlows through Merging - Errors out
That's tough. What you are trying to do is total denormalization (one flat table). That is pretty much the opposite of what Power BI likes (reasonably normalized star or snowflake schema). Merges in Power Query are very expensive.
Dataflows would be the right vehicle as they are basically just that - flat tables supplied by Parquet files. Can you do all the heavy lifting in your upstream system (SQL Server for example) and then spool the result either into a materialized view or into a dataflow?
That's tough. What you are trying to do is total denormalization (one flat table). That is pretty much the opposite of what Power BI likes (reasonably normalized star or snowflake schema). Merges in Power Query are very expensive.
Dataflows would be the right vehicle as they are basically just that - flat tables supplied by Parquet files. Can you do all the heavy lifting in your upstream system (SQL Server for example) and then spool the result either into a materialized view or into a dataflow?
lbendlin
I think i have found both the issue and (in some instances) a work around.
I definetly want to provide access using a fairly simple Star schema but i need to make sure the IDs being joined on are masked, so creating a fake key is a solution i worked through for this. The other element is that the volume of data i was trying to manage just pushed everything too far. So for bigger datasets, i broke them down into logical smaller chunks such as date, or product type for example, this seemed to solve all the immediate ETL errors. Basically PowerQuery was groning under the weight of the data. I also created a few custom queries which solved some of the particularly troublesome flows.
In the end, a combination of custom queries and logically chunked down flows appended or merged later solved the issue. I will continue to make this more efficent over time and thanks for your input again.