Power Query Editor Transform file helper Query folders

Folder listing:

Let's collect all ExecutionAggregation files

Click the first Binary link. That ingests the CSV and already promotes the headers

We can simplify this to 

Table.PromoteHeaders(Csv.Document(Binary), [PromoteAllScalars=true])

and stuff that into a function.

let
    Source = Folder.Files(Folder),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "ExecutionAggregation")),
    Ingest = (Binary)=> Table.PromoteHeaders(Csv.Document(Binary), [PromoteAllScalars=true])
in
    #"Filtered Rows"

Now we add a custom column that ingests each file

let
    Source = Folder.Files(Folder),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "ExecutionAggregation")),
    Ingest = (Binary)=> Table.PromoteHeaders(Csv.Document(Binary), [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Ingest([Content]))
in
    #"Added Custom"

Then we can throw away all the meta data columns we don't need any more

And lastly we expand the Custom column to append all file contents.

That's it. Here is the entire code:

let
    Source = Folder.Files(Folder),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "ExecutionAggregation")),
    Ingest = (Binary)=> Table.PromoteHeaders(Csv.Document(Binary), [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Ingest([Content])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Name", "Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"GatewayObjectId", "AggregationStartTimeUTC", "AggregationEndTimeUTC", "DataSource", "Success", "AverageQueryExecutionDuration(ms)", "MaxQueryExecutionDuration(ms)", "MinQueryExecutionDuration(ms)", "QueryType", "AverageDataProcessingDuration(ms)", "MaxDataProcessingDuration(ms)", "MinDataProcessingDuration(ms)", "Count"})
in
    #"Expanded Custom"