New file will be not uploaded/refreshed by dataflow (only the file will save manual)

[sewi]

Hi all,

I have a problem with one dataflow with the refresh. 

Use case:

Our roboter will be save each day a excel file at our sharepoint folder. 

The dataflow will be refreshed automatically each day and should be pick up the new file with the new data. 

The refresh will be done without errors. But the new data from the excel file are not shown in the dataflow.
If I open the excel file and store the file with the same name at same place and do the refresh again it will be available.

Do you have any ideas which problem with the excelfile /refresh can be there?

BR 

Sebastian

[HarshSathwara19 · Accepted solution]

Recommended Solutions

1. Ensure Proper File Release by the Robot:

   • Verify that the robot properly releases the file after saving. Add a short delay (e.g., 5–10 seconds) between the robot's file-saving process and the dataflow refresh.

2. Validate the File Format:

   • Ensure the robot saves the file in the exact same format (e.g., .xlsx) as required by Power BI. Test the file by manually uploading a file saved by the robot to see if it works as expected.

3. Force Metadata Update:

   • Modify the robot’s script to slightly alter the file (e.g., update a cell or metadata like author or modified date) before saving. This forces Power BI to detect the file as new.

4. Clear SharePoint Cache:

   • Power BI might cache old versions of files in SharePoint. Try enabling the "Skip test connection" option in the dataflow settings or using the SharePoint Folder connector to refresh metadata.

5. Use a File Monitoring Trigger:

   • Instead of scheduling the dataflow refresh, trigger it programmatically using Power Automate. Create a flow that triggers a refresh when the file is modified.

6. Debugging Steps:

   • Open the file saved by the robot and check for any differences compared to the manually saved version. Look for hidden characters, encoding differences, or any structural anomalies.
   • Test the dataflow by directly connecting to the robot-saved file using Power BI Desktop.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
HSathwara.

[rohit1991]

The issue may be due to file locking, metadata caching, or sync delays. Here’s how to fix it:

1. Ensure Proper File Save: Confirm the robot fully saves and releases the file before Power BI refreshes.
2. Force Metadata Refresh: After saving the file, try refreshing the dataflow in Power BI manually or add a delay after saving the file.
3. Check SharePoint Sync: Ensure SharePoint syncs the file immediately for Power BI to detect the update.

This will help ensure Power BI picks up the new data during automatic refreshes.

[v-pnaroju-msft]

Hi sewi,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

[v-pnaroju-msft]

Hi sewi,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[v-pnaroju-msft]

Hi @sewi,

We would like to inquire if the solution offered by @HarshSathwara19 and @jennratten has resolved your issue. If you have discovered an alternative approach, we encourage you to share it with the community to assist others facing similar challenges.
Should you find the response helpful, please mark it as the accepted solution and add kudos. This recognition benefits other members seeking solutions to related queries.

Thank you.

[jennratten · Accepted solution]

Hi @sewi - all of the suggestions listed by @HarshSathwara19 are good.  In your current process, it sounds like the dataflow refresh may just be occurring before the new file is added to the document library. 

You can investigate this by comparing the created date for the new SharePoint file that is added by the robot to the dataflow refresh start time.  If the dataflow refresh start time is equal to/before the file created date or even 1-2 minutes after, the new file may not be readable by the dataflow.  If you find that the dataflow start time is after 1-2 minutes of the file created date, then look to see if the file was checked out - this can sometimes cause it to be unreadable. 

You can also include source file info which the dataflow is using, in the result of the dataflow, so you can see which file was included in the run.

If the objective is for the dataflow to be refreshed when a new file is added to the document library, I agree with @HarshSathwara19 that the best option would be to use Power Automate, which can be created manually or you describe the flow and let Power Automate create it for you.

Please let us know if we can further assist.

[HarshSathwara19 · Accepted solution]

Recommended Solutions

1. Ensure Proper File Release by the Robot:

   • Verify that the robot properly releases the file after saving. Add a short delay (e.g., 5–10 seconds) between the robot's file-saving process and the dataflow refresh.

2. Validate the File Format:

   • Ensure the robot saves the file in the exact same format (e.g., .xlsx) as required by Power BI. Test the file by manually uploading a file saved by the robot to see if it works as expected.

3. Force Metadata Update:

   • Modify the robot’s script to slightly alter the file (e.g., update a cell or metadata like author or modified date) before saving. This forces Power BI to detect the file as new.

4. Clear SharePoint Cache:

   • Power BI might cache old versions of files in SharePoint. Try enabling the "Skip test connection" option in the dataflow settings or using the SharePoint Folder connector to refresh metadata.

5. Use a File Monitoring Trigger:

   • Instead of scheduling the dataflow refresh, trigger it programmatically using Power Automate. Create a flow that triggers a refresh when the file is modified.

6. Debugging Steps:

   • Open the file saved by the robot and check for any differences compared to the manually saved version. Look for hidden characters, encoding differences, or any structural anomalies.
   • Test the dataflow by directly connecting to the robot-saved file using Power BI Desktop.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
HSathwara.