Depending on the qty of columns, you can use an Office Script for it (Run script action in PA). The downsize of this approach is that your data will be retrieved in a bi-dimensional array format, rather than in an array of objects. For example, a table like this:
Will be represented like this in PA:
I ran a test with this same table, but with 99,999 records and it worked fine (17 seconds to run the script), but you can expect some slowness from PA when accessing the raw outputs:
The script itself is pretty simple, and you just need to change the value between parenthesis in the const table line for your actual table name:
function main(workbook: ExcelScript.Workbook) {
const table = workbook.getTable('Table2')
return {data: table.getRangeBetweenHeaderAndTotal().getValues()}
}
To access the output in Power Automate, you can simply use this expression: body('Run_script')['result']['data'].
If you need more instructions on how to create the scripts, you can check the instructions from the following articles:
Let me know if it works for you or if you need any additional help!
-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Accepted Answer.
If this answer helps you in any way, please give it a like.
http://digitalmill.net/
https://www.linkedin.com/in/raphael-haus-zaneti/
