I have lots of files I have created from folders and now I have lots of these cluttering up power Query Editor I really dont like it. Is there any way I can actually get rid of all these and get them down to just one, after its all been done by tweaking some code somewhere?

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"

Ingest = (Binary)=> Table.PromoteHeaders(Csv.Document(Binary), [PromoteAllScalars=true]) As you know each step in Power Query has a name. You can refer to the step in subsequent steps by using that name. Here we create a step that is arbitrarily called "Ingest". Then we provide an argument (Binary) which transforms that step to a function. "=>" completes the function header. After that you can have any number of steps that can eventually return someting. In this case what is returned is the CSV interpretation of the function argument, with the headers already promoted. We need the headers later so we can then combine/append the results correctly. Instead of nesting the instructions you can go through the whole let ... in ... syntax if that's more relatable. And you can also place the function outside this code. There are many ways to do this, and ultimately it is down to personal preference.

Sorry i cant do that its not my data. Hopefully this walk through gives me what I need https://youtu.be/6cGou1-1FOo

These are meant for beginners. Once you checked how they work you will realize how inefficient they are, for example they use the "first" file as the blueprint for the column list of all the files - which may not be what you want. The also read that "first" file twice, for no good reason. Replace them with your own, more streamlined code (via Table.AddColumn and Table.ExpandTableColumn) and then you can delete the groups.

I wouldnt know how to do this. is there any documentation or anything I can use to replace the code and then get rid of these?

Power Query Editor Transform file helper Query folders

12 Replies

lbendlin
Super User
3 years ago
These are meant for beginners. Once you checked how they work you will realize how inefficient they are, for example they use the "first" file as the blueprint for the column list of all the files - which may not be what you want. The also read that "first" file twice, for no good reason.

Replace them with your own, more streamlined code (via Table.AddColumn and Table.ExpandTableColumn) and then you can delete the groups.
- DebbieE
  Community Champion
  3 years ago
  I wouldnt know how to do this. is there any documentation or anything I can use to replace the code and then get rid of these?
  - lbendlin
    Super User
    3 years ago
    here's the general process:
    
    - connect to a folder type data source (file share or sharepoint folder)
    
    - filter for the files you want to ingest
    
    - click the "Binary" link for one of the files, and run the minimum required transform (usually promote headers)
    
    - examine the resulting Power Query code in Advanced Editor and extract the transform step as a function
    
    - remove the steps for the single file
    
    - add a column that calls the function for each of the files
    
    - expand that column
    
    - apply column type changes as needed
    
    You can even specify the function inside the Power Query to keep everything in the same firewall partition.
    
    What is your data source? File folder or SharePoint Document Library?

Forum Discussion

Power Query Editor Transform file helper Query folders

12 Replies

Related content

Query Editor-Hide Transformation Folders

Table Transformation

Transform columns / type

Data transform using power query

Table Transformation