Forum Discussion

DebbieE's avatar
DebbieE
Community Champion
3 years ago

Power Query Editor Transform file helper Query folders

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...
Need Help
lbendlin's avatar
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's avatar
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's avatar
    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?

    • DebbieE's avatar
      DebbieE
      Community Champion
      3 years ago

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

      • lbendlin's avatar
        lbendlin
        Super User
        3 years ago

         

        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"