Remove Top Row not applied to multiple files when using get data from folder

[MaL60903280]

Hi, 

I have tried to combine several files (in the same format) from a folder and to use " remove top row" and "use first row as header". However, after I "close & load", only the first file has been updated with the command. What have I done wrong? Thanks. 

[cengizhanarslan]

You likely applied the "Remove Top Rows" and "Use First Row as Header" steps to the Combined Output Query instead of the Sample File Query. You need to move those cleaning steps into the query that acts as the template for every file.

[Royel]

Hi @MaL60903280 Thanks for asking questions. 

Since you do not mentiond file types, here are two source m-code for excel and csv files. 

Go to Transform Data and use it, you just need to change the file path location: 

Excel: 

let
    Source = Folder.Files("C:\Users\RejaulIslamRoyel\Desktop\data"),
    #"Filtered Files" = Table.SelectRows(Source, each [Extension] = ".xlsx"),
    #"Process Files" = Table.Combine(
        List.Transform(
            #"Filtered Files"[Content],
            each 
                let
                    // Open the Excel workbook
                    Workbook = Excel.Workbook(_, null, true),
                    // Get the first sheet (adjust if needed)
                    FirstSheet = Workbook{0}[Data],
                    // Skip top row(s) and promote headers
                    SkipRows = Table.Skip(FirstSheet, 1),
                    PromoteHeaders = Table.PromoteHeaders(SkipRows, [PromoteAllScalars=true])
                in
                    PromoteHeaders
        )
    )
in
    #"Process Files"

for CSV Files: 

let
    Source = Folder.Files("C:\Users\RejaulIslamRoyel\Desktop\data"),
    #"Filtered Files" = Table.SelectRows(Source, each [Extension] = ".csv"),
    #"Process Files" = Table.Combine(
        List.Transform(
            #"Filtered Files"[Content],
            each Table.PromoteHeaders(
                Table.Skip(Csv.Document(_, [Delimiter=",", Encoding=1252]), 1),
                [PromoteAllScalars=true]
            )
        )
    )
in
    #"Process Files"

Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

I’d love to stay connected. Join me on LinkedIn for more tips, learning paths, and real-world Fabric & Power BI solutions.

[ronrsnfld]

You will need to process the files one at a time, and then combine them.

In the UI, you would process them one at a time, promote the headers, and then probably use the Append Queries method to combine them all.

In M-Code, you would first make a list of all the "binaries" from the Content column of the table from where you have selected which files you wish to combine. Then you can use the List.Accumulate function to process them and combine them.

Sample step:

    #"Files to Process" = #"Previous Step"[Content], //List of all the binaries to process
    #"Process the Files" = List.Accumulate(
        #"Files to Process",
        #table({},{}),
        (s,c)=> Table.Combine(
                    {s,

                    //This next line will need to be changed depending on the file characteristics.
                    //This is an example for CSV files.
                    [x=Csv.Document(c,[Delimiter=",",  Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
                      y=Table.PromoteHeaders(x,[PromoteAllScalars=true])][y]}
                            )
                ),

[DNMAF]

Hi @MaL60903280 ,

it is important that you perform these steps at the right place.

If you do a "folder query" you will have exactly one table in the end. If you perform your steps "remove top row" and so on at this table, you will change only the top rows of that one table.

If you want to perform these steps for every input file you should look for a query called something like "Transform Sample File".  Add your steps to that query. That should work.

Hope that helps!