Import excel file if exists

Hi sabd80 

 

You can use the try .. otherwise construct like so

 

 

let
    Path = "C:\Users\Desktop\Testing no files\Period2\data2.xlsx",
    FileCheck = try File.Contents(Path),
    Output = if FileCheck[HasError] then null else Excel.Workbook(FileCheck[Value])
in
    Output

 

 

 

try File.Contents(Path) – Tries to read the file.

 

if FileCheck[HasError] then null – If an error occurs (file not found), return null (or you can specify an alternate action).

else Excel.Workbook(FileCheck[Value]) – If the file exists, load it as an Excel workbook.

So if the file exists, Output will contain the Excel workbook's contents.  If the file does not exist, it will return null, preventing the code from crashing.

 

Phil

Hi sabd80 ,

PhilipTreacy provided a good suggestion, you can also try the methods in the following links to get it:

error handling - PowerBi - Powerquery: If Folder/File we are loading from a path doesn't exist, change path  

let
    DefaultSource = Folder.Files("C:\DefaultPath"),
    TargetSource = Folder.Files("C:\TargetPath"),
    FileCounter = Table.RowCount(Source),
    DoSomething = if FileCounter > 0 then TargetSource else DefaultSource
in
    DoSomething

Solved: Error handling in loading files from folder - Microsoft Fabric Community

I found a solution for this and wanted to share it:

I created a function to handle the load of the individual Excel sheet. I then use this funtion in to individual queries. One to load the data from the files and one that will return the file name of the files that are unable to be processed by the funtion.

 

The function look like this:

(sourceFile as text) as table =>
let
    Source = Excel.Workbook(File.Contents(sourceFile), null, true),
    ExpandData = Table.ExpandTableColumn(
                Source, 
                "Data", 
                {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15"}, 
                {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15"}
            ),
    AddRowNumber = Table.AddIndexColumn(ExpandData, "Row", 0, 1),

    //Remove Rows with only Nulls
    AddColumnRowWithData = Table.AddColumn(AddRowNumber, "RowWithData", each 
                if [Column1] = null
                    and [Column2] = null
                    and [Column3] = null
                    and [Column4] = null
                    and [Column5] = null
                    and [Column6] = null
                    and [Column7] = null
                    and [Column8] = null
                    and [Column9] = null
                    and [Column10] = null
                    and [Column11] = null
                    and [Column12] = null
                    and [Column13] = null
                    and [Column14] = null
                    and [Column15] = null
                then "Empty Row"
                else "Row with data"
            ),
    RemoveRowsWithNoData = Table.SelectRows(AddColumnRowWithData, each ([RowWithData] = "Row with data")),
    RemoveColomnRowWithData = Table.RemoveColumns(RemoveRowsWithNoData, {"RowWithData"}),

    //Remove Columns with No Name
    TransposeTable = Table.Transpose(RemoveColomnRowWithData),
    RemoveColumnsWithNoName = Table.SelectRows(TransposeTable , each ([Column1] <> null)),
    TransposeTable2 = Table.Transpose(RemoveColumnsWithNoName),

    PromoteHeaders = Table.PromoteHeaders(TransposeTable2, [PromoteAllScalars=true]),
    RenameColumn = Table.RenameColumns(PromoteHeaders,{{"0", "Row"}})

in
    RenameColumn

 

The code for returning a list of files with errors:

let
    FilePath = fnGetParameter("File Path"),
    Source = Folder.Files(FilePath),
    RemoveOtherColumns = Table.SelectColumns( Source, {"Name", "Folder Path"}),
    CallFunction = Table.AddColumn(RemoveOtherColumns, "LoadAllFiles", each fnLoadFiles([Folder Path]&[Name])),
    KeepFilesWithErrors = Table.SelectRowsWithErrors(CallFunction, {"LoadAllFiles"}),
    RemoveColumns = Table.RemoveColumns(KeepFilesWithErrors ,{"Folder Path", "LoadAllFiles"})
in
    RemoveColumns 

---

Best Regards