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 , which works great , but the issue is every file contains  column header , i.e for file first file  I promoted first row as header , but the column header from other files are still present in my data , 

 

Please help to remove column headers of files other than first file , so that I can move further for making report .

 

 

Thanks in advance 

 

Please help nickyvv amitchandak 

 

 

Regards ,

Sujit Thakur 

 

  • 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.

     

7 Replies

    • 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.