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.

[Sebastiendb9]

Thanks, great idea. 👏🏻 I implemented the same. I'll monitor whether this keeps working, so far so good. 🤞🏻

[Cam]

good job!

mines been running for about a month, so far so good 🙂

[Gianluca]

@Cam I was about to do the same ('Job Queue') today, then ended up here while searching for other options. Would you still recommend your solution or did you find some drawbacks? Thank you!

[Anonymous]

Hi @Cam , appreciate this is an old post now but we have run into the same problem. Do you remember roughly how you implemented this queue? I keep running into issues.. First thought was:
1. Have a cloud flow that picks up spreadsheet from sharepoint folder and imports to a pre-staging table
2. Insert a record into a dataflow queue table with a unique guid for the file imported etc. and with a status of Awaiting Processing or something
3. Then have a new cloud flow that triggers upon a record being inserted into this queue table that essentially just sets the status to processing and refreshes the dataflow (this preps the pre-staging table data and does loads of processing and inserts into new dataverse table)
4. Now have another cloud flow with a trigger on when the dataflow refresh completes.. This is the bit I'm unsure how to link the dataflow back to the queue.. I could just grab the records where the status is processing but what happens if 2 files get uploaded - there would be 2 being run concurrently. Did you somehow include the Queue data in the dataflow? I have found no way of doing this. 

Many thanks for any help

[Cam]

@Gianluca 

my Proof of Concept has been going strong for 3 months.

my queue processes ~10 rows per week at the moment.

seems stable for this small number of items.

i've been curious recently, if i could use 'Azure Queue Storage' as an alternative solution.

eg. each dataflow gets it own queue, when the dataflow runs, it pop's the next item out of the start of the queue and reads all of its parameters from that item.

Info and Connector

https://learn.microsoft.com/en-us/azure/storage/queues/storage-queues-introduction

https://learn.microsoft.com/en-us/connectors/azurequeues/

I'm not sure how best to implement this into a Power Automate flow,

but i'm sure this would be a fun challenge 🙂

[Cam]

@Anonymous 

for my POC, the queue table looks like this
(i will be cleaning up my POC in the coming weeks, to build the production solution - design at end of post)

for the POC, i'm just storing GUID's from other tables into the single line of text field.
Bot Log ID is the main parameter that i pass to the Dataflow (if you had many parameters to pass, then you could create a single column called 'parameters', put a JSON object into it, and parse the JSON within the Dataflow to get the parameter values)

here is my dataflow, notice the Parameter group folder on the left, which gets the rows from the queue, filters for the top row, and gets the parameters from it.

these parameters are then used by the Get Job ID and Get Bot ID functions.. and they are used in the following ETL queries.

i have 3x cloud flows which handle processing of the queue (this is serious overkill, i will simplify this in the production solution) [please ignore Ingest - wait for dataflow... that one is used by a parent flow for something else]

Dataflow Scheduler: triggers every 5 minutes, checks if there are any new 'jobs' (rows) in the dataflow queue table. if there is a job then it checks if the dataflow from that job is busy or not. if it is not, then it starts the dataflow. note: the queue is used by multiple dataflows... its up to you if you have one queue per dataflow or one queue which handles all dataflows.

Ingest - End Dataflow monitors the DataflowRefreshHistories table (default table in CDM).
when a new row is added to the table, it will update the associated record in the queue table,
and copy the success/failure status into the queue table. it also removes the next in queue flag.

Ingest - Start Dataflow just adds a new row into the queue, and saves the variables into the row

[Cam]

@Gianluca @Anonymous if you could share how you've created your queues / queue-monitor-flow / dataflows that would be awesome to see how you've done this?

[Cam]

hello @bbarka 

"4. Now have another cloud flow with a trigger on when the dataflow refresh completes.. This is the bit I'm unsure how to link the dataflow back to the queue.. I could just grab the records where the status is processing but what happens if 2 files get uploaded - there would be 2 being run concurrently. Did you somehow include the Queue data in the dataflow? I have found no way of doing this. "

i ran into this same issue.

i made the queue work on two assumptions:

- each dataflow can only be executed once at a time (i prevent concurrent execution of the same dataflow, by waiting until the dataflow is idle before executing it)

- yes the dataflow itself uses its GUID to filter the queue, and then it gets the first row, and gets its parameters from that row.

[Anonymous]

@Cam this is excellent stuff and exactly what I need, thank you so much! I was getting hung up worrying about if they drop lots of files in the sharepoint folder together - as currently this will set off 10 x cloud flows which each refresh the dataflow but I can see that you are instead scheduling them to run every 5 mins and then ensuring run synchronously. Seems like a really good approach. I only have one dataflow but this could be run multiple times per day and the dataflow could update 000s records each time. At the moment I have one cloud flow that does the following:
1. Triggers on file added to sharepoint folder
2. Creates record in dataverse to say file has been uploaded
3. Reads header data from file
4. Does a foreach on main data in excel file and copies to pre-staging dataverse table with status = NotProcessed
5. Refreshes dataflow (this gets data from pre-staging table with status=NotProcessed and does a lot of processing/ETL with this data and inserts this into another dataverse Staging table)
6. Another trigger in same cloud flow on refresh completing of that dataflow (this works bizarrely having another trigger further down the cloud flow - but I need in same flow to know which records to update in pre-staging table below)
7. Update all prestaging records to be status=Processed where same file

The above actually works ok but problem is I have to put 1 min delays between step 4-5 or sometimes it will kick off the dataflow refresh before all the records are in the prestaging table.. I also need a 1 min delay after the dataflow refresh complete trigger (between step 6-7) as bizarrely it appeared that even though the trigger was when the refresh completed - it was like sometimes it thought it had completed but it hadn't actually!
The other problem with my approach is that the dataflow is being refreshed everytime a file is uploaded which isn't strictly necessary e.g. if they upload 10 files then it would be better to put all 10 files in pre-staging table and then refresh the dataflow once. 
Yours seems better approach as not relying on these triggers and having to put 1 min delays in hurts my sole and feels flaky as hell. Thank you I will have a go at implementing this. Like you say would be interesting to see @Gianluca approach too.

[Gianluca]

@Anonymous Hi. Unfortunately, I had to deprioritize this due to work-load. I might come back to this in Q2 2023 (I hope...).