Forum Discussion

MaL60903280's avatar
MaL60903280
New Member
8 months ago
Solved

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

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 ...
Need Help
  • Royel's avatar
    Royel
    8 months ago

    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's avatar
ronrsnfld
Super User
8 months ago

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