Fernitudela.dev

In this Video and post (I recommend you to watch the video where we go through the step by step in detail… and also, for the same price, I recommend you to suscribe to the channel 😊) we are going to see how you can troubleshoot the issue of not having access to the data of an Excel file that is not in an Excel table.

The problem

let’s say we have an integration (real scenario I faced) and we receive an Excel like this one:

where the information is not inside an Excel table. When we go to get the data from Power Automate, all the actions for data consumption need you to specify a table.

So we have to first create the table. The first option you might think, to keep everything within Power Automate, is to use the action Create Table, but we need to know the range prior to create the table using that action, since our integration will receive different Excels with different Rows, we cannot set a fixed range. The solution that I found and used is through an Excel script.

Solution

We can create a reusable and generic Excel script that will receive a plain Excel without tables and it will create the table for the used range. The idea is to keep the Script as simple as possible, with the aim of just clear the obstacule without introducing any logic here.

function main(workbook: ExcelScript.Workbook) {
    // Your code here
    let firstWorkSheet = workbook.getFirstWorksheet();

    let fullUsedRange = firstWorkSheet.getUsedRange();

    workbook.addTable(fullUsedRange, true);
}

I am not fan of using Excel scripts with Power Automate, since they don’t work pretty good with Environment Variables to set the path and it is a pain to maintain different versions across different environments, but in this case, always that we are facing a simple plain Excel, with just one Worksheet, we can use this same Excel Script, and no much maintainance or modifications to it should be expected.

Assuming we have a folder in OneDrive where we receive the plain Excels files, and we locate the script in a different folder in One Drive too:

Then our Power Automate will look like this:

being every single step like this:

Optional step, check that no tables exist prior to run the script. This way, I left the door open to, if there are already a table in the file, implement the logic in the false block to get the table name and use it directly.

And finally we can access the newly created table:

And if we test it:

We can see how it read the information in our plain Excel file!! isn’t it cool?