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

     

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]))

     

    • Deevo_'s avatar
      Deevo_
      Resolver I
      10 months ago

      m_dekorte 

      I have tinkered and tried to adjust both the getFiles and base query. 

       

      The getFiles does not give any syntax error, but these are the 2 changes I added:

      1) replacing "(substring as text) as table =>" with "(folderPath as text, substring as text) as table =>"
      2) replacing "Invoked = Table.AddColumn(NoHidden, "Transform File", each #"Transform File"([Content]))," with "Invoked = Table.AddColumn(NoHidden, "Transform File", each Excel.Workbook([Content])),"

       

      Working version of "getFiles" before implementing changes:

       

      (substring as text) as table =>
      let
      Source = Folder.Files("\\internal\Datafiles\Quarters\"),
      KeepRootOnly = Table.SelectRows(Source, each [Folder Path] = "\\internal\Datafiles\Quarters\"),
      KeepData= Table.SelectRows(KeepRootOnly, each Text.Contains([Name], substring, Comparer.OrdinalIgnoreCase)),
      Latest = Table.FirstN(Table.Sort(KeepData, {{"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(KeepData) then error "no data" else KeptCols

       

      Not working version of "getFiles" AFTER implementing 2 changes:

       

      (folderPath as text, substring as text) as table =>
      let
      Source = Folder.Files("\\internal\Datafiles\Quarters\"),
      KeepRootOnly = Table.SelectRows(Source, each [Folder Path] = "\\internal\Datafiles\Quarters\"),
      KeepData= Table.SelectRows(KeepRootOnly, each Text.Contains([Name], substring, Comparer.OrdinalIgnoreCase)),
      Latest = Table.FirstN(Table.Sort(KeepData, {{"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(KeepData) then error "no data" else KeptCols

       

      What happens when I load the base query and click on every applied step from the top:

      When I try to load the base query, the second step "Invoke" returns an 'Error' when it previously returned 'Table'.

       

      I can't seem to understand what is happening. Any ideas?

       

       

       

      • m_dekorte's avatar
        m_dekorte
        Resident Rockstar
        10 months ago

        Hi Deevo_ 

         

        To resolve this more quickly, I mocked up data and a solution, see attached.

        Let me know if you have any questions.