Forum Discussion

Beyondforce's avatar
Beyondforce
Icon for Helper I rankHelper I
9 years ago
Solved

Clean Up the folders in the Query panel!?

Hey All,   Is it possible to connect to a folder and import all the files so that the Query panel will be clean with only the Table name? As you can see in the picture, it looks really messy with ...
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    You can include everything in your main query.

     

    You can copy the "Transform Sample File..."  code into the #"Invoke Custom Function1" step and replace the parameter by [Content].

     

    Additionally, you need to adjust the #"Expand Table Column1" step, so the column names for the expanded table are derived from the first table in the column with the nested tables.

     

    Now you don't need the other query objects any more. To be honest I had some difficulties removing them and I created a new pbix file, but maybe you can just remove them. 

     

    An example below; adjust as appropriate.

     

    let
        Source = Folder.Files(".................."),
        #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".csv")),
        #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Rows", "Transform File from Binary", 
            each let
                    Source = Csv.Document([Content],[Delimiter="	", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
                    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
                 in
                    #"Promoted Headers"),
        #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
        #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Binary"}),
        #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Binary", Table.ColumnNames(#"Invoke Custom Function1"[#"Transform File from Binary"]{0})),
        #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Test1", Int64.Type}, {"Test2", Int64.Type}})
    in
        #"Changed Type"