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 

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

       

      • Deevo_'s avatar
        Deevo_
        Resolver I
        10 months ago

        m_dekorte The updated code is now working as expected. I had to make some tweaks to get it to work. Original results against the new results are correct. I can now delete the Helper queries!

        I hope this can help others achieve a similar outcome. Thank you so much for your time.

         

        **Update**

        FYI only, I published this to the PowerBI Report Server and setup a scheduled refresh it it fails with the error "[Unable to combine data]......Please rebuild this data combination".

         

        How I fixed this: I combined the "getFiles" and "Base Query" into one query and I referred to this post: Solved: [unable to combine data] Please rebuild this data ... - Microsoft Fabric Community

         

         

        See below query where i added comments:

        /*Added "let getFiles = " to the beginning*/

        let

        getFiles = (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, /*Added a comma to the end*/

         

        /*Removed the "let" from here*/

        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