Anyone figured out a nifty way to pass parameters to a dataflow?

[arpost]

I love Power Platform dataflows but am finding the lack of a formal way to pass parameters to them rather frustrating. I was wondering if the community has come up a nifty workaround for implementing a parameter-like functionality. Anyone have some brilliant ideas?

One of my use cases is I want to trigger a dataflow on a file when a person pushes a button in an app, but the dataflow needs to then interact with that SPECIFIC file in a folder and no others. I've got a Power Automate flow set up to refresh the dataflow.

[Cam]

i've had some people ask how i'm ingesting a file into the dataflow... so i'm leaving this here.

hopefully this helps someone

===========

I have a dataverse table, with a File column, and the binary file is stored in that column.

the dataflow gets the binary content of that file, using the web connector, and the dataverse API.

here is an example file (this is a .CSV.... but works with other types of files too), uploaded to a table

this is your source step, in your dataflow.

use the Web.Contents connector (green) with the Dataverse API endpoint (red) in order to get the file's binary content (blue)

once you have the files binary content (blue)... you can transform it as per usual methods.

for example: in this next step, i open the file as a .CSV, and the following steps do filtering/sorting/stuff

hope this helps 

have a nice day

thank you

[foxpower]

Hi all, I've recently built a solution with a similar use case to OP, and others in this thread.

My solution consists of:

1. A Power Automate Cloud Flow, which I refer to as the ‘Dispatcher’.
2. A Power Automate Cloud Flow, which I refer to as the ‘Performer’.
3. A Power Automate Work Queue.
4. A Power Apps Dataflow.

This is my solution at a high level.

When a file is created in the SharePoint library, it triggers the Dispatcher cloud flow. The dispatcher flow grabs some metadata from the file and creates an item in the Work Queue. This has concurrency control set to 1, to ensure that 1 file always results in 1 work queue item. For my use case, this isn’t a performance issue as there are only 3 csv files created in the SP library, once every 24 hours.

Here is an example of the Work Queue Item which the Dispatcher Flow creates in the Work Queue. The link to the SharePoint file, and some other metadata is included as a JSON string in the ‘input’ column of the Work Queue item. The beauty of this is that you can easily modify the dispatcher flow to include as much metadata as you like, without affecting the logic of anything. You can then use that metadata in subsequent steps, for logging etc.

When a new item is created in the Work Queue, it triggers the Performer cloud flow. The Performer cloud flow has trigger conditions:

1. The work queue ID of the new item must match the value specified in an environment variable (EV) (this EV is critical for managing deployments through multiple environments!!).
2. The status of the new item must equal processing.
3. A particular piece of metadata specific to my use case, is the ‘Name’ (which refers to a customer name).
   1. This allows me to use one Work Queue, with multiple performer flows. This is helpful if there are significant differences between the steps required for one customer’s file compared to another.

The trigger conditions are important if you have multiple Work Queues, and/or dispatchers and performers in your environment – if not implemented, you’ll have a mess of flows triggering based on the wrong event.

Once triggered, the Performer flow calls the relevant Power Apps Dataflow to refresh – and importantly – waits for it to finish refreshing. If you don’t have this wait-until-refresh-completes-step you would need to include a delay action, otherwise you will likely have issues where a Performer flow attempts to refresh a dataflow whilst it is already mid-refresh.

Here is the step within the Power Apps Dataflow which queries the Work Queue Items Dataverse table, filters based on the ID of the Work Queue, and for items with processing status.

Once I find the Work Queue Item I want, I use the json() function to extract the link to the SharePoint file.

It is worth noting here that the Dataflow gets upset if you do not trim the parameters from the URL.

Now that I have the URL to the SharePoint file, I run a separate query to extract and manipulate the data from the actual .csv file.

Finally, I load this into the Dataverse tables that I ultimately present to the user via a Model Driven App. Full script below for reference.

Hope this is helpful to someone! Would love to hear from anyone on how this approach could be improved or otherwise. 

[foxpower]

Hi all, I've recently built a solution with a similar use case to OP, and others in this thread.

My solution consists of:

1. A Power Automate Cloud Flow, which I refer to as the ‘Dispatcher’.
2. A Power Automate Cloud Flow, which I refer to as the ‘Performer’.
3. A Power Automate Work Queue.
4. A Power Apps Dataflow.

This is my solution at a high level.

When a file is created in the SharePoint library, it triggers the Dispatcher cloud flow. The dispatcher flow grabs some metadata from the file and creates an item in the Work Queue. This has concurrency control set to 1, to ensure that 1 file always results in 1 work queue item. For my use case, this isn’t a performance issue as there are only 3 csv files created in the SP library, once every 24 hours.

Here is an example of the Work Queue Item which the Dispatcher Flow creates in the Work Queue. The link to the SharePoint file, and some other metadata is included as a JSON string in the ‘input’ column of the Work Queue item. The beauty of this is that you can easily modify the dispatcher flow to include as much metadata as you like, without affecting the logic of anything. You can then use that metadata in subsequent steps, or logging etc.

When a new item is created in the Work Queue, it triggers the Performer cloud flow. The Performer cloud flow has trigger conditions:

1. The work queue ID of the new item must match the value specified in an environment variable (EV) (this EV is critical for managing deployments through multiple environments!!).
2. The status of the new item must equal processing.
3. A particular piece of metadata specific to my use case, is the ‘Name’ (which refers to a customer name).
   1. This allows me to use one Work Queue, with multiple performer flows. This is helpful if there are significant differences between the steps required for one customer’s file compared to another.

This allows me to ensure that the Performer flow is only triggered when a new item is created that meets certain criteria. This is important if you have multiple Work Queues, and/or dispatchers and performers in your environment – if not implemented, you’ll have a mess of flows triggering based on the wrong event.

Once triggered, the Performer flow calls the relevant Power Apps Dataflow to refresh – and importantly – waits for it to finish refreshing. If you don’t have this wait-until-refresh-completes-step you would need to include a delay action, otherwise you will likely have issues where a Performer flow attempts to refresh a dataflow whilst it is already mid-refresh.

Here is the step within the Power Apps Dataflow which queries the Work Queue Items Dataverse table, filters based on the ID of the Work Queue, and for items with processing status.

Once I find the Work Queue Item I want, I use the json() function to extract the link to the SharePoint file.

It is worth noting here that the Dataflow gets upset if you do not trim the parameters from the URL.

Now that I have the URL to the SharePoint file, I run a separate query to extract and manipulate the data from the actual .csv file.

Finally, I load this into the Dataverse tables that I ultimately present to the user via a Model Driven App. Full script below for reference.

Hope this is helpful to someone! Would love to hear from anyone on how this approach could be improved or otherwise.