MicrosoftI'm trying the following process, as it avoids the need to handle conflicts of duplicate entries (i.e. an entry is added from an implemented Automate process but also added once the file is re-opened). This could be fixed by adding extra steps in Power Automate to validate the Excel, but am attempting to avoid the hassle by implementing the following Automate process instead:
New Forms response -> Get Response details -> Add item to SharePoint List
I have created a new SharePoint List, imported the existing Excel data and set up this process for future Forms responses (there will be no conflicts with the opening of Excel). Then the original Excel will still update anytime someone opens it, but is otherwise untouched and the automatically updated List is an independent process. For linking the data to Power BI, I am redirecting it to the SharePoint List with the added bonus that instead of linking in the numerous Forms' spreadsheets, I can direct all responses to the same SharePoint List in the automation process, since they are all of a similar format (i.e. creating columns in the List as 'Form', 'Question', 'Response', etc.). Am now trying to go one step further so that instead of scheduling the Power BI dataset to refresh as we have previously done from the Excel responses, it refreshes from the SharePoint List whenever an entry is added - i.e. my dashboard becomes close to Real-Time.
Has anyone tried anything similar to this?
I think the best way after this is to create a dataflow from the SharePoint List to build the Power BI dashboard from, as this allows me to implement an Incremental Refresh to the dataset. Now instead of previously my Power BI dashboard having to refresh all of the Forms generated Excel files, it can refresh only the past day's List entries (i.e. looking for any new Forms submissions that will generate from the Automate process), and store all historic data. In theory, the process will be very similar to the old way, but a lot more efficient and less of a performance hit when refreshing.
