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

(0) Share

Report

Posted on by molegris

157

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

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (3)

Answers (0)

EricRegnier 8,720 Most Valuable Professional on at

Like (1)

Report

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!

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report

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!

Was this reply helpful? Yes No
molegris 157 on at

Like (1)

Report

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

Was this reply helpful? Yes No