At this moment, I am having following steps like this:
Step 1: Query yesterday data from a SQL Server. It looks like that:
SELECT
ImageSawStepID,
ImageID,
SawSkillID,
ProductionWorkerID,
WorkingTimeInMilliseconds,
LastModifyDatetime
FROM RemoveBackground.dbo.ImageSawStep
WHERE CONVERT(DATE, LastModifyDatetime) = '2023-06-06'
AND WorkingTimeInMilliseconds IS NOT NULL
Step 2: Then, I export to CSV file
Step 3: Then I append the CSV file to existing table in Power Query Dataflow to analyze data
These 3 steps repeated day by day
I want to automate this process.
Can I do this in Power Automate? Please show me
The prompt I use in ChatGPT : “how to get data of yesterday automatically from SQL Server, and appending to DataFlow Power Query via Power Automate”
Hi @hunghahuy - First and 2nd step can be automated. What are you doing in 3rd step, are you uploading the csv File to DataFlow and appending it to existing table. If yes, then don’t think it can be automated.
What can be done is to maintain a Csv file that have historic data. Daily append the new data in the Csv file using Power Automate. Use this csv as source for Dataflow that can be automated by using Normal refresh process.
Do explain if you are doing something different in 3rd step.
In Step 3, I just found another option according to your advice.
I will keep the CSV file created from step 2 as source, but will keep them in a sharepoint folder. It means there will be new csv file daily created and save in the sharepoint folder.
then I can use that sharepoint folder as a source to combine all the csv files and load to dataflow, then keep refreshing dataflow, dataset from that daily.
Following this means that the 3rd step can be broken down into small steps (3.1 and 3.2, etc)
With this option, do you think I can automate the whole process using Power Automate.
If yes, could you please show me the detail steps. I am still getting stuck in step 1 when using Power Automate
Due to inactivity, we’d like to conclude that you no longer require assistance to solve your inquiry.
If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.