Forum Discussion

Deevo_'s avatar
Deevo_
Resolver I
10 months ago
Solved

Expression.Error: There weren't enough elements in the enumeration to complete the operation

Hi all,

When I schedule a report refresh on the PowerBI report server, I noticed that when a data file is not available, the refresh will fail and I receive error message "Expression.Error: There weren't enough elements in the enumeration to complete the operation".

 

I have data files which will only appear each quarter as the time passes and are all in the same format.

 

For example, for Quarter 1, the data file will be titled "Quarter 1 data.xlsx". Then for Quarter 2, the data file witll be titled "Quarter 2 data.xlsx". Then "Quarter 3 data.xlsx" and lastly "Quarter 4 data.xlsx".

 

What I would like to do in Powerquery:

  • Check for the 4 data sources each time a refresh happens.
  • Append the 4 files together
  • Skip any data file that are not yet available.

 

I have been searching for a solution which would skip the files if they did not exist, but cannot find such solution. Maybe I am searching the wrong terminology.

 

How can I modify my below queries to make this happen?

 

Here is my current code:

Quarter 1 query:

let

Source = Folder.Files("\\internal\Datafiles\Quarters\"),

#"Filtered Rows1" = Table.SelectRows(Source, each [Folder Path] = "\\internal\Datafiles\Quarters\"),

#"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", each Text.Contains([Name], "Quarter 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", each #"Transform File"([Content])),

#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),

#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1",{"Source.Name", "Date modified", "Transform File"}),

#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),

#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Date modified", type date}, {"Date", type date}, {"Product", type text}, {"Costs", type number}})

in

#"Changed Type"

 

Quarter 2 query:

let

Source = Folder.Files("\\internal\Datafiles\Quarters\"),

#"Filtered Rows1" = Table.SelectRows(Source, each [Folder Path] = "\\internal\Datafiles\Quarters\"),

#"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", each Text.Contains([Name], "Quarter 2")),

#"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", each #"Transform File"([Content])),

#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),

#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1",{"Source.Name", "Date modified", "Transform File"}),

#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),

#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Date modified", type date}, {"Date", type date}, {"Product", type text}, {"Costs", type number}})

in

#"Changed Type"

 

Quarter 3 query:

let

Source = Folder.Files("\\internal\Datafiles\Quarters\"),

#"Filtered Rows1" = Table.SelectRows(Source, each [Folder Path] = "\\internal\Datafiles\Quarters\"),

#"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", each Text.Contains([Name], "Quarter 3")),

#"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", each #"Transform File"([Content])),

#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),

#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1",{"Source.Name", "Date modified", "Transform File"}),

#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),

#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Date modified", type date}, {"Date", type date}, {"Product", type text}, {"Costs", type number}})

in

#"Changed Type"

 

Quarter 4 query:

let

Source = Folder.Files("\\internal\Datafiles\Quarters\"),

#"Filtered Rows1" = Table.SelectRows(Source, each [Folder Path] = "\\internal\Datafiles\Quarters\"),

#"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", each Text.Contains([Name], "Quarter 4")),

#"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", each #"Transform File"([Content])),

#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),

#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1",{"Source.Name", "Date modified", "Transform File"}),

#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),

#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Date modified", type date}, {"Date", type date}, {"Product", type text}, {"Costs", type number}})

in

#"Changed Type"

 

Append Query:

 

let

Source = Table.Combine({#"Quarter 1 data", #"Quarter 2 data", #"Quarter 3 data", #"Quarter 4 data"}),

 

in

#"Source"

 

 

Many Thanks!!!

Need Help
Tips and Tricks
  • m_dekorte's avatar
    m_dekorte
    10 months ago

    Hi Deevo_ 

     

    Let's try to restructure your approach to handle missing files, first create this helper and call it: getFiles

    (substring as text) as table =>
let
    Source = Folder.Files("\\internal\Datafiles\Quarters\"),
    KeepRootOnly = Table.SelectRows(Source, each [Folder Path] = "\\internal\Datafiles\Quarters\"),
    KeepQuarter = Table.SelectRows(KeepRootOnly, each Text.Contains([Name], substring, Comparer.OrdinalIgnoreCase)),
    Latest = Table.FirstN(Table.Sort(KeepQuarter, {{"Date modified", Order.Descending}}), 1),
    NoHidden = Table.SelectRows(Latest, each [Attributes]?[Hidden]? <> true),
    Invoked = Table.AddColumn(NoHidden, "Transform File", each #"Transform File"([Content])),
    Renamed = Table.RenameColumns(Invoked, {"Name", "Source.Name"}),
    KeptCols = Table.SelectColumns(Renamed, {"Source.Name", "Date modified", "Transform File"})
in
    if Table.IsEmpty(KeepQuarter) then error "no data" else KeptCols

     

    Next use a base query to invoke it and test file availability before the append.

    let
    Source = Table.FromColumns(
        {{"Quarter 1", "Quarter 2", "Quarter 3", "Quarter 4"}},
        type table [Substring = text]
    ),
    Invoke = Table.AddColumn(Source, "Invoked", each getFiles([Substring])),
    NoErrors = Table.RemoveColumns(Table.RemoveRowsWithErrors(Invoke, {"Invoked"}), {"Substring"}),
    Expand1 = Table.ExpandTableColumn(NoErrors, "Invoked", {"Source.Name", "Date modified", "Transform File"}),
    Expand2 = Table.ExpandTableColumn(Expand1, "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    Typed = Table.TransformColumnTypes(Expand2,{{"Source.Name", type text}, {"Date modified", type date}, {"Date", type date}, {"Product", type text}, {"Costs", type number}})
in
    Typed

     

    It might need a little tweek but I trust it will get you there...
    If you run into problems, report back. Cheers.

  • m_dekorte's avatar
    m_dekorte
    10 months ago

    Good observation and no worries Deevo_ 

    Give this updated getFiles function a go.

    (folderPath as text, substring as text) as table =>
let
    Source = Folder.Files(folderPath),
    KeepRootOnly = Table.SelectRows(Source, each [Folder Path] = folderPath),
    KeepQuarter = Table.SelectRows(KeepRootOnly, each Text.Contains([Name], substring, Comparer.OrdinalIgnoreCase)),
    Latest = Table.FirstN(Table.Sort(KeepQuarter, {{"Date modified", Order.Descending}}), 1),
    NoHidden = Table.SelectRows(Latest, each [Attributes]?[Hidden]? <> true),
    Invoked = Table.AddColumn(NoHidden, "Transform File", each Excel.Workbook([Content], true, true){[Item="Data",Kind="Sheet"]}?[Data]?),
    Renamed = Table.RenameColumns(Invoked, {"Name", "Source.Name"}),
    KeptCols = Table.SelectColumns(Renamed, {"Source.Name", "Date modified", "Transform File"})
in
    if Table.IsEmpty(KeepQuarter) then error "no data" else KeptCols

     

    Here's the Base query as well

    let
    Source = Table.FromColumns(
        {{"Quarter 1", "Quarter 2", "Quarter 3", "Quarter 4"}},
        type table [Substring = text]
    ),
    Invoke = Table.AddColumn(Source, "Invoked", each getFiles(folderPath, [Substring])),
    NoErrors = Table.RemoveColumns(Table.RemoveRowsWithErrors(Invoke, {"Invoked"}), {"Substring"}),
    Expand1 = Table.ExpandTableColumn(NoErrors, "Invoked", {"Source.Name", "Date modified", "Transform File"}),
    Expand2 = Table.ExpandTableColumn(Expand1, "Transform File", {"Date", "Product", "Costs"} ),
    Typed = Table.TransformColumnTypes(Expand2,{{"Source.Name", type text}, {"Date modified", type date}, {"Date", type date}, {"Product", type text}, {"Costs", type number}})
in
    Typed

     

    Keep in mind that you'll need to update the folderPath in the Invoke step of the Base query.

    Invoke = Table.AddColumn(Source, "Invoked", each getFiles(folderPath, [Substring])),

     

10 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    10 months ago

    Hi Deevo_ 

     

    Let's try to restructure your approach to handle missing files, first create this helper and call it: getFiles

    (substring as text) as table =>
let
    Source = Folder.Files("\\internal\Datafiles\Quarters\"),
    KeepRootOnly = Table.SelectRows(Source, each [Folder Path] = "\\internal\Datafiles\Quarters\"),
    KeepQuarter = Table.SelectRows(KeepRootOnly, each Text.Contains([Name], substring, Comparer.OrdinalIgnoreCase)),
    Latest = Table.FirstN(Table.Sort(KeepQuarter, {{"Date modified", Order.Descending}}), 1),
    NoHidden = Table.SelectRows(Latest, each [Attributes]?[Hidden]? <> true),
    Invoked = Table.AddColumn(NoHidden, "Transform File", each #"Transform File"([Content])),
    Renamed = Table.RenameColumns(Invoked, {"Name", "Source.Name"}),
    KeptCols = Table.SelectColumns(Renamed, {"Source.Name", "Date modified", "Transform File"})
in
    if Table.IsEmpty(KeepQuarter) then error "no data" else KeptCols

     

    Next use a base query to invoke it and test file availability before the append.

    let
    Source = Table.FromColumns(
        {{"Quarter 1", "Quarter 2", "Quarter 3", "Quarter 4"}},
        type table [Substring = text]
    ),
    Invoke = Table.AddColumn(Source, "Invoked", each getFiles([Substring])),
    NoErrors = Table.RemoveColumns(Table.RemoveRowsWithErrors(Invoke, {"Invoked"}), {"Substring"}),
    Expand1 = Table.ExpandTableColumn(NoErrors, "Invoked", {"Source.Name", "Date modified", "Transform File"}),
    Expand2 = Table.ExpandTableColumn(Expand1, "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    Typed = Table.TransformColumnTypes(Expand2,{{"Source.Name", type text}, {"Date modified", type date}, {"Date", type date}, {"Product", type text}, {"Costs", type number}})
in
    Typed

     

    It might need a little tweek but I trust it will get you there...
    If you run into problems, report back. Cheers.

  • Deevo_'s avatar
    Deevo_
    Resolver I
    10 months ago

    m_dekorte 

    This took me about one hour to work out how to implement, after some very minor tweaks. it works wonders. 

    I tested 2 scenarios:

    • Scenario 1: Only Quarter 1 file is available and the 3 other files are missing.
      • This works fine.
    • Scenario 2: Quarter 1 file is available and has data. Quarter 2, Quarter 3 and Quarter 4 files are available, but all these 3 quarters (2,3, and 4) have no data, just a blank file. 
      • I can see that all the files will still load even if there is no data inside the files. This is exactly what i need.

    One important thing i forgot to add was there is an additional "Helper Queries" that is automatically created when i added the data source "Folder". This is the "Tranform File" query. I noticed you have picked up on this in my 4 original queries, so Thank you.

     

    Below is how my query currently flows. Can you please have a look and provide your feedback and recommend how I can clean it up?

     

    1) Load Power Query --> get Data --> Folder (apply all other filtering here: name, sort descending etc). Then from here this automatically creates the "Transform File" helper query.

    2) Create the "getFiles" (This is where i got stuck for an hour and was not sure where this was to be created, so i just created this under the "Other Queries" section and not inside the "Tranform Files" section. This is working fine, but appears abit messy.)

    3) Create the base query to test file availability and then append.

    4) Close and Load

     

    I appreciate you

    • m_dekorte's avatar
      m_dekorte
      Resident Rockstar
      10 months ago

      Hi Deevo_ 

       

      Great to hear that you’ve got it working!

       

      Since you didn’t share the code for the #"Transform File" query, I left it as it was to make sure the outcome stayed the same. However, the logic you described - “apply all other filtering here: name, sort descending, etc.” - may already be part of the getFiles function. Specifically, this is likely handled in the steps:

      KeepRootOnly → KeepQuarter → Latest.

       

      If that’s the case, applying additional filtering in the #"Transform File" query would be redundant.

      With a few small tweaks to the custom function, you could eliminate that entirely. Previously, I didn’t parameterize the connection string in the Source step of the getFiles function, but that’s easy to fix. Also, the Invoked step now transforms the Binary [Content] (your XLSX files) into a table - assuming no other logic is being applied by the #"Transform File" query.

      (folderPath as text, substring as text) as table =>
let
    Source = Folder.Files(folderPath),
    KeepRootOnly = Table.SelectRows(Source, each [Folder Path] = folderPath),
    KeepQuarter = Table.SelectRows(KeepRootOnly, each Text.Contains([Name], substring, Comparer.OrdinalIgnoreCase)),
    Latest = Table.FirstN(Table.Sort(KeepQuarter, {{"Date modified", Order.Descending}}), 1),
    NoHidden = Table.SelectRows(Latest, each [Attributes]?[Hidden]? <> true),
    Invoked = Table.AddColumn(NoHidden, "Transform File", each Excel.Workbook([Content])),
    Renamed = Table.RenameColumns(Invoked, {"Name", "Source.Name"}),
    KeptCols = Table.SelectColumns(Renamed, {"Source.Name", "Date modified", "Transform File"})
in
    if Table.IsEmpty(KeepQuarter) then error "no data" else KeptCols

       

      Now the process could look like this:
      (1) Create a new blank query, for the getFiles function.

      (2) Optionally, place it in a Group to organize queries in the Queries pane.

      (3) Create the base query that invokes getFiles with the added parameter in the Invoke step, like so: 

      getFiles("\\internal\Datafiles\Quarters\",[Substring])

       

      • Deevo_'s avatar
        Deevo_
        Resolver I
        10 months ago

        m_dekorte I really appreciate your time to guide me and help me understand this. As this is something I have been trying to solve for a long long time now. I have one last barrier I need to get through before I can implement this solution across all my reports.

         

        I noticed in your latest code snippet that it is still referencing "Transform File". I tried removing it from the "base query" and it did not like it because it is calling another helper query called "Sample File". In the "Sample File" query i noticed there are transformations being done in the applied steps.

         

        This is the code from the original "Transform file" query, 

         

        let
        Source = (Parameter1 as binary) => let
        Source = Excel.Workbook(Parameter1, null, true),
        Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true])
        in
        #"Promoted Headers"
        in
        Source

         

        This is the code from the "Sample File" query:

        let

        Source = Folder.Files("\\internal\Datafiles\Quarters\"),

        #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "Quarter 1")),

        #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date modified", Order.Descending}}),

        #"Filtered Rows1" = Table.SelectRows(#"Sorted Rows", each ([Folder Path] = "\\internal\Datafiles\Quarters\")),

        #"Kept First Rows" = Table.FirstN(#"Filtered Rows1",2),

        #"Filtered Hidden Files1" = Table.SelectRows(#"Kept First Rows", each [Attributes]?[Hidden]? <> true),

        Navigation1 = #"Filtered Hidden Files1"{0}[Content]

        in

        Navigation1

         

         

        Questions:

        1. How can I remove the "Helper Queries" completely?

        Thanks!