Forum Discussion

Sujit_Thakur's avatar
Sujit_Thakur
Solution Sage
6 years ago
Solved

Combine files

Dear all ,  I have number of files in a folder , in which every file is data for each day . Now my task I to combine these files and make a BI report . I am using get data from folder  option , wh...
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Sujit_Thakur - The key here is getting the Promoted Headers step into the correct query. The query you want to edit should be called Transform Sample File. Here is an example of the query you want to edit (below). Any steps that are in this query should be performed for each file in the folder.

     

Sujit_Thakur's avatar
Sujit_Thakur
Solution Sage
6 years ago

Greg_Deckler  please help , I know you have answered such questions before , but to do it with excel files from a folder .

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I tried loading a folder of excel files. It is loading fine. Only the first file's header is loaded. For rest of the files, it takes only the data.

     

     

    Following is the auto-generated power query.

     

    let
    Source = Folder.Files("C:\Users\Sreenath\Documents\TestFiles"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Item", type text}, {"Qty", Int64.Type}})
in
    #"Changed Type"

     

     

    There is no step in the process where I had to explicitly "promote" the 1st row as headers. It was kind of automatic. But you mentioned that you did it. 

     

    Can you compare the power query here with the power query in your file? Can you post a screen shot of the step where you are promoting the first row as headers? It will  help.

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      6 years ago

      Sujit_Thakur - The key here is getting the Promoted Headers step into the correct query. The query you want to edit should be called Transform Sample File. Here is an example of the query you want to edit (below). Any steps that are in this query should be performed for each file in the folder.