Working with large excel files - Power Virtual Agent
I am building a PVA chatbot that would allow users to get details of their ticket using multiple input criteria (ticket ID, date range, customer number etc.). I have used Power Automate to connect to the Excel data source. Lately I came to know that Power automate would only allow me to fetch limited rows of data from excel and even if I am able to increase this limit, the query time to fetch the desired result back to PVA increases tremendously. The excel file has nearly 180K rows.
I wanted to know if there's a better way (a different data source/connector) to get such output to my PVA chatbot without losing data or sacrificing tremendous amount of time?
@MicRoBot I'm doing something similar (different type of data) where I'm having Power Automate call a PowerBI query. If you do that, you can pass along the criteria from your customer through the query itself and that should limit the returned values.
You'd need a PowerBI Pro license but then you can add the data you're currently storing in Excel as a blank table (assuming you don't need to refresh it). You may also be able to connect directly to your ticketing system through Power BI too.
You'd basically want to create the query and filter the table based on the fields you would pass through. Then you can easily copy the Query text from the Performance Analyzer (in Power BI Desktop). Then once you paste that into the Query Text (3rd field) in Power Automate - you can replace the filter you had on the visual with the variable you passed in through PVA.
After that Parse JSON is a very valuable function to pull the data you want out.
Thanks @gregmarbais,! for the above information. I did find a similar solution to the problem. Power Automate has connector called "Run script" when you try to connect it to an excel data source. Through this I was able to send multiple parameters to the excel file and run an automated script in the excel environment. Finally the results were returned to PA in JSON format which I could parse to get the required values. This method still takes time if the returned results contained multiple rows of data, however, it helped me overcome the limitation where I was only able to put one filter criteria to fetch data using "List Rows in a Table" action.