Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Copilot Studio - Calling Actions from Copilot S... / Working with large exc...
Copilot Studio - Calling Actions from Copilot S...
Unanswered

Working with large excel files - Power Virtual Agent

(0) ShareShare
ReportReport
Posted on by MicRoBot 6

Hi,

 

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?

Categories:
Power Automate Flows
Flow Integration
  • MicRoBot Profile Picture
    MicRoBot 6 on at
    Re: Working with large excel files - Power Virtual Agent

    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.

  • gregmarbais Profile Picture
    gregmarbais 108 on at
    Re: Working with large excel files - Power Virtual Agent

    @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.

    gregmarbais_0-1664195192553.png

     

    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.

    Let me know if I can answer more questions!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Thomas Rice – Community Spotlight

We are honored to recognize Thomas Rice as our March 2025 Community…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,508 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 65,436 Most Valuable Professional

Leaderboard

Featured topics

Product updates

Microsoft Power Platform Community release plans