As described in this post – https://www.ehansalytics.com/blog/2020/2/15/avoid-using-excel-xls-files-as-data-source –
there are issues to be aware off when you use xls files instead of xlsx in Power Query. See also this thread
Answering the twitter started by Imke Feldmann (https://twitter.com/TheBIccountant) thread by Ruth Pozuelo (go follow her excellent youtube – channel – link) encouraged me to write this post – – as I claimed we can convert the xls files to xlsx using Power Automate.
So here is a guide on how to do it
Convert xls files to xlsx
In the scenario I will use a trigger when an e-mail is received and use a rest API provided by https://cloudconvert.com/.
OBS – This is a paid service where you pay by the minute the conversion takes – price from $0.02 to $0.01 per minute.
First we start by selecting to build an automated flow and select the trigger “When a new email arrives (V3)”
Set the advanced options to only trigger when Attachments is included and include the attachments in the following steps in our flow.
As the next step I use an Azure Blob storage to store the file in the e-mail. When selecting the output from the previous step – power automate will automatically create an Apply to each container in which we can refer to each attachment in the mail.
In the Create Blob Action I connect to a blob storage and load the attachment into a preload folder
Now add a step where we create an HTTP request within the Apply to each container
In order to use the cloud convert REST API we need first to create a Process and use the process ID to create the conversion – documentation here
In the body property you specify your own APIKey and tell the process you want to do is a conversion from xls format to xlsx.
Next – add another HTTP request
We use a POST request again
And in the URI we use the result from the previous step as it returns a unique address to the process id from cloud convert
In the Body property we specify where
Here is the final overview of the steps needed.
Now send an email to the inbox you have connected your trigger to run.
And in our preload folder we can see the files
And in the converted folder we have the converted xlsx files
Hope this can help you converting your xls files to xlsx.
This will also make it much easier if you want to be able to setup