Forum Discussion
Please help with Dataflow(s)
Yes, I think that would be the most efficient setup i.e. one 'base' data flow that brings in everything, then a number of other tables that reference (not duplicate) the base dataflow, but are filtered as required for the other different reports.
If your source that feeds the dataflow is an SQL server, I think the most efficient in terms of load may be connecting directly to the source from each report, as you could then make full use of query folding and staggered refresh times, but YMMV.
Pete
Hi jcampbell474 ,
You can't do this using a dataflow. You can do this via Query Folding when working with SQL servers, but a dataflow is essentially just a Power Query transformation in itself, so the opportunity for query folding has already passed by the time it comes into your model.
Obviously I don't know what kind of data volume you're working with, or how many adjustments you're looking to make, but an individual dataflow filtered to your requirements for each report may be your best bet here.
If you're on Premium Capacity, you can reference your main dataflow and filter the reference result to your requirements. This should reduce some of the processing load in producing multiple outputs.
Pete
Thank you. Yes, we're on premium capacity.
To make sure I understand, what do you mean by "filter the reference result to your requirements"? Simply filter the dataflow itself to what's needed in the dataset?
Edit: Are you referring to having a 'base' dataflow, then create filtered dataflows sourced from it?
Yes, I think that would be the most efficient setup i.e. one 'base' data flow that brings in everything, then a number of other tables that reference (not duplicate) the base dataflow, but are filtered as required for the other different reports.
If your source that feeds the dataflow is an SQL server, I think the most efficient in terms of load may be connecting directly to the source from each report, as you could then make full use of query folding and staggered refresh times, but YMMV.
Pete