How to add error redundancy to current Power Query code if data source is empty

[Deevo_]

Hi All,

I receive an error "Error: There weren't enough elements in the enumeration to complete the operation.".

I want to add error redundancy to my query below, but unsure how.

Scenario:

• I currently have a single folder as my data source and within this folder a new data file gets created every financial quarter. So the max number of data files is 4.
  • For example, Data File 1 contains 3 months of data (July, Aug and Sep), Data File 2 contains (Oct, Nov, Dec), Data File 3 contains (Jan, Feb Mar), Data File 4 contains (Apr, May, June).
• Each file has a separate data loading query to find the latest file in the folder and also has a corresponding "Helper Query".
• When the report refreshes, it checks for Data File 1, then Data File 2, Data File 3, Data File 4 and loads them. I have another query to Append all 4 data files together.

Issue:

• When a report refresh occurs and one of the data files is not in the folder location (because it has not been created yet), I get the Error.

Question:

• How can i implement error redundancy into my "data loading query" and my "helper query" so the refresh is successful when a datasource is empty?

Many thanks in advance!

My current "data loading query":

let
Source = Folder.Files("\\ReportData\FY_Data\"),
#"Filtered Rows1" = Table.SelectRows(Source, each [Folder Path] = "\\ReportData\FY_Data\"),
#"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", each Text.Contains([Name], "Data - 1")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date modified", Order.Descending}}),
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",1),
#"Filtered Hidden Files1" = Table.SelectRows(#"Kept First Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1",{"Source.Name", "Date modified", "Transform File (2)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Date modified", type date}, {"Pers.No.", Int64.Type},
{"Date", type date}, {"Org. Unit", Int64.Type}, {"Full Name", type text}, {"Emp. Type.", type text}, {"User Name", type text}, {"Position", type text},
{"Start date", type date}, {"End Date", type date}, {"Project Name", type text}})
in
#"Changed Type"

My current "Help query":

let
Source = Folder.Files("\\ReportData\FY_Data\"),
#"Filtered Rows1" = Table.SelectRows(Source, each [Folder Path] = "\\ReportData\FY_Data\"),
#"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", each Text.Contains([Name], "Data - 1")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date modified", Order.Descending}}),
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",1),
Navigation1 = #"Kept First Rows"{0}[Content]
in
Navigation1

[Deevo_]

Hi Dino,

First off, thanks for helping out. I really appreciate this!

1. I have a question about the "data loading query". You mentioned "but make sure to check for null before proceeding". Am I suppose to add something else to the query on top of what you alrteady stated?
2. In regards to both the "data loading query" and "the helper query", I am not getting any syntax errors, but I am still receiving the expression error for queries that cannot locate a data file. Is this related to the question above?
3. After updating the query with your updates, I click "Close & Apply" and I get a Load error "There weren't enough elements in the enumeration to complete the operation.". Most likely related to the null values issues, but not too sure?

[Anonymous]

Hi @Deevo_ ,

To implement error redundancy in your Power Query for situations where a data file might be missing, you can use the 'try...otherwise...' construct to handle errors gracefully. This construct allows you to attempt an operation and specify an action to take if an error occurs.

"data loading query"

let
    Source = Folder.Files("\\ReportData\FY_Data\"),
    #"Filtered Rows1" = Table.SelectRows(Source, each [Folder Path] = "\\ReportData\FY_Data\"),
    #"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", each Text.Contains([Name], "Data - 1")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date modified", Order.Descending}}),
    #"Kept First Rows" = try Table.FirstN(#"Sorted Rows",1) otherwise null,
    // Include the rest of your steps here, but make sure to check for null before proceeding
    ....
    //
in
    #"Changed Type"

"help query"

let
    Source = Folder.Files("\\ReportData\FY_Data\"),
    #"Filtered Rows1" = Table.SelectRows(Source, each [Folder Path] = "\\ReportData\FY_Data\"),
    #"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", each Text.Contains([Name], "Data - 1")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date modified", Order.Descending}}),
    #"Kept First Rows" = try Table.FirstN(#"Sorted Rows",1) otherwise null,
    Navigation1 = if #"Kept First Rows" <> null then #"Kept First Rows"{0}[Content] else null
in
    Navigation1

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.