Forum Discussion
Data format error. external table is no tin the expected format for (.xls file format)
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.
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
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]),
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.