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_ 

 

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.

    • Deevo_'s avatar
      Deevo_
      Resolver I
      10 months ago

      Hi m_dekorte,

      I apologise that this is taking so long to solve. But i only just realised now that after looking at your spreadsheet that, there is one critical component that is in the "Helper Queries" that I totally missed. The helper query must navigate to a specific worksheet called "Data" within each of the data files. I "think" that the getFiles query is not performing this step?

       

       

      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 original "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

      • m_dekorte's avatar
        m_dekorte
        Resident Rockstar
        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])),