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.

Murtaza_Ghafoor's avatar
Murtaza_Ghafoor
Super User
7 months ago

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"