Data Synchronization and Refresh Issue in Power BI and apps

[essentialist_24]

Query 1: Data Synchronization and Refresh Issue
Problem: We are experiencing significant delays in data synchronization from a Power Apps form (used for user input) to an Excel file stored in OneDrive, which serves as a data source for Power BI. Updates made in Power Apps can take up to 6 hours to reflect in the Excel file, leading to lagged data in Power BI reports. Additionally, automatic refresh of the data source is not occurring as expected.
Question: What are the best practices or solutions to reduce the data synchronization time between Power Apps and Excel to ensure more immediate data refreshes in Power BI reports?

Query 2: Data Loss Issue in Table Merging
Problem: We are attempting to retrieve the latest entry based on a timestamp by merging two tables, sorting by timestamp, grouping by Record ID (for handling duplicate entries), and selecting the first entry from each group. However, this approach leads to data loss, as there can be multiple relevant entries within each group that are not captured.
Question: How can we adjust our approach to ensure that all relevant entries within each group are retained, rather than only the first entry, to avoid data loss?

Any insights, best practices, or alternative methods to address these challenges effectively would be greatly appreciated.

[rohit1991]

Hi @essentialist_24 ,


Query 1: Reducing Data Sync Time

1. Use Dataverse instead of Excel for faster Power BI refreshes.
   
   
   
2. Set up a Power Automate Flow to sync data in real-time after Power Apps submissions.
   
   
   
3. Optimize the Excel file (reduce file size, remove complex formulas).
   
   
   
4. Enable Incremental Refresh in Power BI for quicker updates.
   
   

Query 2: Preventing Data Loss in Table Merging

1. Use Filter Logic to keep all entries within a time range instead of grouping.
   
   
   
2. Join with a Distinct List of Record IDs to retain all matching timestamps.
   
   
   
3. Custom Aggregation in Power Query to define conditions for relevant entries.