Power Query when add a new excel files to a folder and get Error

[Pol2all]

Hi all,

some help needed, once I connect to a folder to select all files (excel with the same structure) into that folder and prepare all transformations it works very well.

But if I add a new excel file (same structure) into that folder and do Refresh All, I get the following error:

DataFormat.Error: The input couldn't be recognized as a valid Excel document.
Details:
    Binary

(Note: if I start a new Power BI instance and repeat all transfomation steps, but with the new excel file already in the folder, everthing works very well)

Any ideas, please? thanks

[agnieszka]

Hi All,
I have struggled with the same issue. But didn't want to give up and finnaly resolved it!
My situation: I was getting xlsx file (with no table, just 1 sheet in it) inot my outlook every day. 
This was essential file for my Power BI, to be updated every day.
After moving file to OneDrive or to Sharepoint, I was getting an error msg : Details: "The input couldn't be recognized as a valid Excel document." .

Solution: After speaking to a sender of this xlsx file , checking few things (like opening the file via notepad++) showed us that coding of that xlsx was incorrect.
The was was changed from xlsx to csv with UTH-8 encoding.

And everything is working just fine 🙂
Hope it helps anyone!

[Manoj_Nair]

@Pol2all- Try follow these steps, seems some issue with the newly added Excel file in the folder during the refresh process

1. Verify file format and structure: Confirm that the newly added Excel file has the same format and structure as the existing files like file extension is ".xlsx" or a compatible Excel format that Power BI supports.
2. Refresh specific files: Instead of refreshing all the files in the folder at once, try refreshing the newly added Excel file individually.

3. Convert Excel files to CSV: If the issue persists with Excel files, consider converting the files to CSV format and using the CSV files as a data source in Power BI.

If this post helps to find solution would be happy if you could mark my post as a solution and give it a thumbs up

Best regards

Manoj Nair

Linkedin - https://www.linkedin.com/in/manoj-nair-%E2%98%81-344666104/

[Pol2all · Accepted solution]

hi @mahenkj2 

after read the post on the link shared, that I thank you.

I've noticed that the problem can be solved just by open and close the EXCEL file, without any change or editing. So, following an example on one of the reply related to the shared post, I've adapt a powershell script, that I run each time that any new files is placed into any subfolder. After I can refresh all in the PowerBI without having the error

$fileList = Get-ChildItem -path "C:\anydirectory" -Recurse| %{$_.FullName}

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

foreach ($file in $fileList) {
$file
$wb = $excelComObject.Workbooks.Open($file, $true, $false)
$wb.Save()
$wb.Close()
}

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

Thanks

[mahenkj2]

hi @Pol2all 

Does it help you?

https://community.fabric.microsoft.com/t5/Power-Query/The-input-couldn-t-be-recognized-as-a-valid-Ex....