Forum Discussion
Power Query Editor Transform file helper Query folders
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?
Oh my word. im just as confused now. they are in sharepoint but I have used Folder to get the data in
Ive tried going through this list but Im struggling . I just clicked Binary on a file and it just shows this.
I guessed next to click the arrows on Table
But it hasnt brought through the file name. Which I really need and happened when I just used Folder.
"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"
And this section, I havent got a clue what to do here..
This is the Advanced Editor Code from the original Get Data Folder that uses these helper files
let
Source = Folder.Files("C:\Users\Me\tenant\Project\Data"),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "Metric")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (11)", each #"Transform File (11)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (11)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (11)", Table.ColumnNames(#"Transform File (11)"(#"Sample File (11)"))),
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"and stuff that into a function.
this is confusing me. i dont know what this part means. I know I can click a Query and Create function but I dont know how this all works with the given logic unfortunately
Im starting to wonder if this isnt too big and complex a job because I have a lot of queries to change
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.