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. 

[V-yubandi-msft]

Hi @MaL60903280,

Everything should be clear now. If you need any additional information, please let me know.

Thank you.

[V-yubandi-msft]

Hi @MaL60903280 ,

If you’ve had a chance to review the responses shared by the @Hans-Georg_Puls,  @cengizhanarslan  and @cengizhanarslan  please let us know whether they helped resolve your issue.

You can also review the guidance shared by other community members for additional context.

If you still need any clarification, we’d be happy to assist further.

Thank you all.

[Olufemi7]

Hi @MaL60903280, 

Apply your steps in the Transform Sample File query. That query is the template Power Query uses to process every file in the folder.
If you only apply changes in the combined output query, they’ll affect just the first file.

See Microsoft’s docs: Combine files overview 

[Murtaza_Ghafoor]

I think no one has given you a proper answer, i believe you are missing this option.
Combine & Transform and then select suggested option and or you need to fix one file(Transform File) and write a custom M code like i did here in below example

let
Source = Folder.Files("C:\Users\DELL\Desktop\Fabric Learning"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File (2)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Name", type text}, {"Sales", Int64.Type}, {"Region", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Source.Name", Order.Descending}}),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Sorted Rows", {{"Source.Name", each Text.AfterDelimiter(_, "_"), type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Extracted Text After Delimiter",".xlsx","",Replacer.ReplaceText,{"Source.Name"}),
#"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "Custom", each if [Source.Name] = "Mar" then 3 else if [Source.Name] = "Jan" then 1 else if [Source.Name] = "Feb" then 2 else null, type number),
#"Sorted Rows1" = Table.Sort(#"Added Conditional Column",{{"Custom", Order.Ascending}})
in
#"Sorted Rows1"

[Murtaza_Ghafoor]

let
Source = Folder.Files("C:\Users\DELL\Desktop\Fabric Learning"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File (2)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Name", type text}, {"Sales", Int64.Type}, {"Region", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Source.Name", Order.Descending}}),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Sorted Rows", {{"Source.Name", each Text.AfterDelimiter(_, "_"), type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Extracted Text After Delimiter",".xlsx","",Replacer.ReplaceText,{"Source.Name"}),
#"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "Custom", each if [Source.Name] = "Mar" then 3 else if [Source.Name] = "Jan" then 1 else if [Source.Name] = "Feb" then 2 else null, type number),
#"Sorted Rows1" = Table.Sort(#"Added Conditional Column",{{"Custom", Order.Ascending}})
in
#"Sorted Rows1"

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

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]}
                            )
                ),

[Hans-Georg_Puls]

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!