Combine files

[Sujit_Thakur]

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 

[Sujit_Thakur]

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

[Anonymous]

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 · Accepted solution]

@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]

Thank you @Greg_Deckler  sir 

[amitchandak]

@Sujit_Thakur , Are you merging file. If you are merging. Then merge will take care of header ?

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[Sujit_Thakur]

@amitchandak @nickyvv  please help 

[Sujit_Thakur]

Dear @amitchandak ,

I am not mergibg , I am using get data and then selecting folder as a option and selecting a primary sheet type to extract data from all the files in folder