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

[Deevo_]

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!!!

[m_dekorte · Accepted solution]

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

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

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

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

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

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

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

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 · Accepted solution]

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

@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

[m_dekorte · Accepted solution]

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.