topic Re: Data format error. external table is no tin the expected format for (.xls file format) in Power Query

Data format error. external table is no tin the expected format for (.xls file format)

Krishna09 — Fri, 18 Aug 2023 12:54:44 GMT

Hello,

from power automate every day a file is created in .xls format.

and this file is connected o power query editor(other excel) in sharepoint. But if I save same file in .xlsx format it works. is there any possiblity to automate the process?

So is there a way to solve this error.

Re: Data format error. external table is no tin the expected format for (.xls file format)

Anonymous — Fri, 18 Aug 2023 14:31:53 GMT

Hi there!
Shame that you didnt post Mcode for this query.

Without the code I can just give you this advise:
Power automate: since it is creating the file, surly there must be a way to save it as XLSX.
If this for any reason is not doable:

in power query, you need to change your M code a bit.
This Mcode works with XSLX file right?
This means the query is using to formulas: Excel.Workbook(FileContent( ... ))
However in my expierience with XLS file it actualy needs CSV.Document formula.
So:
1. Open blank work book.
2. using power query - import this XLS filee using from text/ CSV
3. Copy the formula from formula bar from power Query.
4. Close and Save.
5. go into your file with the error.
6. your current importing formula (probobly called Source -> enter the formula bar.
7. Replace current formula with:
try (Current formula) otherwise NewCopiedformula

Im sorry but without more info / code i cannot do more.

Re: Data format error. external table is no tin the expected format for (.xls file format)

Krishna09 — Fri, 18 Aug 2023 15:08:43 GMT

Thank you for your quick response,

As per your suggestion, I tried to import xls file from text/csv file. then it showed empty folder.

Below is Mcode while importing .xls file from SharePoint:-

I have tried few ways to import xls file from sharepoint.

I have created empty xls files and saved in sharepoint and imported them. it was successfull. So i used that Mcode renamed files and tried to import. then it gave similar error.

below is the Mcode I used

the error started at "Invoke Custom Function1"

Re: Data format error. external table is no tin the expected format for (.xls file format)

Anonymous — Mon, 21 Aug 2023 08:03:44 GMT

ok, I see ur still using basic sharepoint import.
as I stated, without the exact file it is hard towork on.

So what you can do is to create CSV.Document() command for exact same file (import from text / csv) see what code it generated (the square brackets CSV paraameters)
then what you need to do is to combine formulas in Mcode:
it would look sth like this:
CSV.Document(Sharepoint.files( ... ), [Parameters] )
and try it this way.

Re: Data format error. external table is no tin the expected format for (.xls file format)

Krishna09 — Mon, 21 Aug 2023 08:39:57 GMT

Thank you Anonymous for your suggestion.

Below is Mcode from csv file importing.

let

Source = SharePoint.Files("https://sharepoint.com/teams/", [ApiVersion = 15]),

#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "Dashboard 2021 has completed") and [Extension] = ".csv"),

#"Dashboard 2021 has completed csv_https://sharepoint com/teams/Report - Flow/Excel_file_Renamed/" = #"Filtered Rows"{[Name="Dashboard 2021 has completed.csv",#"Folder Path"="https://sharepoint.com/teams/Report - Flow/Excel_file_Renamed/"]}[Content],

#"Imported CSV" = Csv.Document(#"Dashboard 2021 has completed csv_https:// sharepoint com/teams/Report - Flow/Excel_file_Renamed/",[Delimiter=";", Columns=44, Encoding=1252, QuoteStyle=QuoteStyle.None]),

#"Changed Type" = Table.TransformColumnTypes(#"Imported CSV",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}})

#"Changed Type"

Below is the Mcode for .xls file, I have replaced [Parameter] values of csv in .xls file. it gave belo error. If i have replaced the parameter wrong, please guide me.

let

Source = SharePoint.Files("https:// sharepoint.com/teams /", [ApiVersion = 15]),

#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], " Dashboard") and [Extension] = ".xls"),

#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Name] = " Dashboard 2021 has completed.xls" )),

#" Dashboard 2021 has completed xls_https:// sharepoint com/teams/ Report - Flow/Excel_file_Renamed/" = #"Filtered Rows1"{[Name=" Dashboard 2021 has completed.xls",#"Folder Path"="https://sharepoint.com/teams/ Report - Flow/Excel_file_Renamed/"]}[Content],

#"Imported Excel Workbook" = Excel.Workbook(#"Dashboard 2021 has completed xls_https:// sharepoint com/teams/ Report - Flow/Excel_file_Renamed/",[Delimiter=";", Columns=44, Encoding=1252, QuoteStyle=QuoteStyle.None])

#"Imported Excel Workbook"

Re: Data format error. external table is no tin the expected format for (.xls file format)

Anonymous — Mon, 21 Aug 2023 09:04:37 GMT

hmmm,
Question:
in the first querry you have imported CSV file into power query.
in the second code you want to import XLS file thats acualy the CSV file from query before.

so: why cant you reference the CSV importing query?
Since you have excel file that is importing CSV file, you can either connect directly to that XLSX file / Power BI file instead of trying to import again xls file.

In PQ in1 file there is no need to doubly import files like this.
however, if the CSV importing file is in separete workbook, you can create connection to that XLSX / power BI file tothat specific query result

Re: Data format error. external table is no tin the expected format for (.xls file format)

Krishna09 — Mon, 21 Aug 2023 09:18:46 GMT

Anonymous ,

Regarding the point you mentioned so: why cant you reference the CSV importing query?, I didnot understand (do you mean, while importing .xls file, can I refer it to csv importing query?).

Firstly this .xls file is extracted and saved in sharepoint every day using power automate flow.

To import xlsx or csv file in power query, I have to manually save xls file as xlsx or csv. to avoid this manual work, I am looking for a solution to import xls file which is sharepoint.

This is the reason, I cannot import xlsx or csv format file in power qery in this scenario.

secondly the data is not in dataverse, it is a workbook file in sahrepoint folder.

Re: Data format error. external table is no tin the expected format for (.xls file format)

Anonymous — Mon, 21 Aug 2023 13:30:53 GMT

ok let me try this way:
You posted a code that is importing data from CSV file:

let Source = SharePoint.Files("https://sharepoint.com/teams/", [ApiVersion = 15]), #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "Dashboard 2021 has completed") and [Extension] = ".csv"), #"Dashboard 2021 has completed csv_https://sharepoint com/teams/Report - Flow/Excel_file_Renamed/" = #"Filtered Rows"{[Name="Dashboard 2021 has completed.csv",#"Folder Path"="https://sharepoint.com/teams/Report - Flow/Excel_file_Renamed/"]}[Content], #"Imported CSV" = Csv.Document(#"Dashboard 2021 has completed csv_https:// sharepoint com/teams/Report - Flow/Excel_file_Renamed/",[Delimiter=";", Columns=44, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Changed Type" = Table.TransformColumnTypes(#"Imported CSV",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}}) in #"Changed Type"

This is Mcode, which means it can be found in 1 of 2 places:
1. Excel file
2. Power BI file

So if for any reason you need this refference in atnoher excel file, you can simply use Excel.Workbook(Folder.Files())
or excel workbook (sharepoint.files() )

also the formula CSV.Document do not have point to CSV files alone but to txt and XLS files also

Re: Data format error. external table is no tin the expected format for (.xls file format)

Krishna09 — Mon, 21 Aug 2023 16:18:52 GMT

Anonymous , based on your suggestion, using csv Mcode I replaced csv with xls. then complete sheet data is converted in the form of html coding. As shown below

Mcode:-

#"Imported CSV" = Csv.Document(#"Dashboard 2021 has completed xls_https:// sharepoint com/teams/ Report - Flow/Excel_file_Renamed/",[Delimiter=";", Columns=44, Encoding=1252, QuoteStyle=QuoteStyle.None]),

Re: Data format error. external table is no tin the expected format for (.xls file format)

Anonymous — Thu, 24 Aug 2023 05:26:28 GMT

Hi Mate,
Im afraid I have no other ideas currently.
Without access or correct schematics of connecctions, I do not know what else to suggest.