Import excel file if exists

[sabd80]

Hi,
I import data from multiple folders in to Power BI, sometimes the file does not exists in some folders, how can handle error if the file does not exit, and import the file if it exists.

I have below code that check if the file exsists or not, but I don't know how to import the file if exists.

let
Path = "C:\Users\Desktop\Testing no files\Period2\data2.xlsx",
Result = try Binary.ToText(File.Contents(Path)),
Output =if Logical.From(Result[HasError]) then "not exist" else "exist"
in
Output

[PhilipTreacy · Accepted solution]

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

[Anonymous · Accepted solution]

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, chan... 

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

[PhilipTreacy · Accepted solution]

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