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 we...
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])),

     

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!

      • m_dekorte's avatar
        m_dekorte
        Resident Rockstar
        10 months ago

        Hi Deevo_ 

         

        Those queries are only referenced in the Expand2 step from the base query: 

        Table.ColumnNames(#"Transform File"(#"Sample File"))

        Replace that section by a hard coded list of column names, in the Expand2 step:

        {"Date", "Product", "Costs"}

        The the original "Transform file" query, is creating an 'expanded table' and the "Sample File" query logic is covered, with the above adjustment they can be removed.

         

        This step, just adds a column named "Transform File" but does not reference that query.

        Invoked = Table.AddColumn(NoHidden, "Transform File", each Excel.Workbook([Content]))