cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
molegris
Advocate III
Advocate III

Dataflows - Is there a way to skip queries when source files are missing?

Hi,

 

I created a dataflow to load 6 dimensions in Dataverse tables from flat files (csv) dropped by users into a Teams SharePoint directory.  4 dimensions are very slow changing, they need updates from twice a year to only once every 2 years.  The other 2 dimensions would benefit from a daily or at least weekly update.

 

Whether the dataflow is scheduled, triggered manually or by Power Automate (I hope this will be possible soon), the problem is that not all 6 dimensions have to be updated and some source files are missing.  Hence the dataflow constantly fails!

 

I temporarily fixed the problem by removing the part of the process that moves the csv files to the archive after they have been loaded, and by asking users to overwrite existing files when they move them to the drop-in folder in Teams.  Then the dataflow always loads all 6 dimensions with old and new files.  I find this solution hideous! For many reasons.

 

Is there a way to skip queries inside a dataflow when their source file is missing?

Can anybody suggest a better design?

 

Am I even using the right product??  I mean, dataflows work well for a one-shot data import but I’ve been struggling, and honestly very disappointed, with the data integration capabilities of dataflows to replicate standard ETL processes.  In my opinion it’s still far away from SSIS!

 

Thank you

--mo

3 REPLIES 3
EricRegnier
Super User
Super User

Hi @molegris,

Unfortunately there is no Dataflow feature to skip  if the source is missing. Feel free to submit that idea though at: https://powerusers.microsoft.com/t5/Power-Apps-Ideas/idb-p/PowerAppsIdeas

 

Dataflows is a great low code/no code tool to import data from CSV. So I would say you have the right tool especially if you don't have a lot of data (<10k) and simple data model. The common Power Platform alternate if you need more control is Power Automate. In a flow you would be able to trigger the import when a new CSV is uploaded in Teams.

Hope this helps!

EricRegnier
Super User
Super User

Hi @molegris,

Unfortunately there is no Dataflow feature to skip if the source is missing. Feel free to submit that idea though at: https://powerusers.microsoft.com/t5/Power-Apps-Ideas/idb-p/PowerAppsIdeas

 

Dataflows is a great low code/no code tool to import data from CSV. So I would say you have the right tool especially if you don't have a lot of data (<10k) and simple data model. The common Power Platform alternate if you need more control is Power Automate. In a flow you would be able to trigger the import when a new CSV is uploaded in Teams.

Hope this helps!

Hi Eric,

 

Thank you for your reply.  I'll submit the idea.

 

As for Power Automate, I did try it in the past and I really liked the ability to trigger the process when a new CSV file is detected.  I abandoned it because the data transformations were painful without the power of the M query language.  I know the recently added Power Query to the SQL connector... I hope they'll extend this function further is future releases.

 

Meanwhile I'll continue to experiment.  My new idea is to add to each query a dummy TableFromRecord to merge with the content of the CSV file.  Whit the proper Try (error handling) I should be able to import the single dummy record when there is no CSV…. Which should be fast and at least not fail.

 

have a nice day

--mo

Helpful resources

Announcements
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Users online (2,411)