The input couldn't be recognized as a valid Excel document

Just had the same problem, but our data was already in the table format.

Oddly enough, changing it back to a standard data range ended up fixing it for us.

An alternate solution for anyone else getting stuck with this.

This problem arises when the Excel workbooks have cells with malformed URLs.

As a workaround, you can create an environment variable named "PQ_DisableNet45Containers", with a value of "true".

We are working on a fix that we hope to include in the September release of Power BI Desktop.

Sorry for the incovenience this is causing.

Yes the best solution is running Powershell script and automated it through SQL Agent on a server or automating via window scheduler if on your personal computer.  If you are running several files you can use the following two files

Config file

{
"LogFile" : "OpenSaveExcel.log",
"Files":
[

"C:\Program Files\test1.xlsx",
"C:\Program Files\test2.xlsx",
"C:\Program Files\test3.xlsx"
]
}

Powershell script:

$config = Get-Content 'config.json' | Out-String | ConvertFrom-Json
$fileList = $config.Files
$logFile = $config.LogFile

function WriteLog
{
param ([string]$logString)
Write-Host $logString
Add-content $logFile -value $logString
}

$excelComObject = New-Object -COM "Excel.Application"
$excelComObject.DisplayAlerts = $false

foreach ($file in $fileList) {
$timeStamp = (Get-Date).toString("yyyy/MM/dd HH:mm:ss")
WriteLog "$timeStamp Processing file $file"
try {
$fileHandle = Get-Item -Path $file -erroraction stop
$fileHandle.IsReadOnly = $false
$wb = $excelComObject.Workbooks.Open($file, $true, $false)
$wb.Save()
$wb.Close()
} catch {
WriteLog "$timeStamp Error: $_"
}
WriteLog "$timeStamp Done processing file $file"
}

$excelComObject.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelComObject)
Remove-Variable excelComObject

This creates a error log and also runs the command to open the file, save, then closes the file. 

Risks are permission issues if running on a virtual machine or server you may need to adjust for that when running remotely.