Power Query Combine Multiple Files From Folder

[MarkFlrz]

Hi community,

I'm working on combining 90 excel files from a folder.
But the data combined from all files is compiling vertically and I need to change it so I can choose only the columns I need from each file.
Columns are consistent within all files being combined

[v-prasare]

May I ask if you have resolved this issue? If so, Can you please share the resolution steps here. This will be helpful for other community members who have similar problems to solve it faster.
If we don’t hear back, we’ll go ahead and close this thread. For any further discussions or questions, please start a new thread in the Microsoft Fabric Community Forum we’ll be happy to assist.
Thank you for being part of the Microsoft Fabric Community.

[v-prasare]

@MarkFlrz,

We would like to confirm if our community members answer resolves your query or if you need further help. If your query still not resolved can you please provide sample data as @raisurrahman requested? this will help find solution easily.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[v-prasare]

@MarkFlrz,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

@MasonMA & @Royel  ,Thanks for your prompt response

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[Royel]

Hi @MarkFlrz  as you mention you have 90 excel file and wants to load while keeping only selected columns. 

Lets do it like this. 

Step 1: Get Data > From Folder -> Click "Transform Data"

Step2: Give a name "data" and keep only  Content and Name column 

as you can see, i have total 3 excel files. 

Now, create a blank query and pest this in advance editor 

let
    Source = data,
    Combined = Table.ExpandTableColumn(
        Table.AddColumn(Source, "Data", 
            each Table.PromoteHeaders(Excel.Workbook([Content]){0}[Data])),
        "Data", {"Invoice No", "PO No", "Customer", "Amount", "Date"}, 
        {"Invoice No", "PO No", "Customer", "Amount", "Date"}),
    Cleaned = Table.SelectColumns(Combined, 
        {"Invoice No", "Date", "Amount"})
in
    Cleaned

Results: 

As you can see, in total i have 5 columns but i am selecting only 3 columns. You need to update this column name.

Here is sample  https://drive.google.com/drive/folders/1OWAVP7hoqbRNiCgphJuF7s23466vTx9G?usp=drive_link 

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

[MasonMA]

@MarkFlrz 

Hi, if all files share schema (same set of columns), then when you “Combine & Transform” in Power Query, you actually don’t get FileA (for example) and FileB separately anymore.  You get one appended query with all columns.

If you need to choose columns from each individual files, i'd suggest creating Query Reference.

In the Reference Query, select your File name and Columns you need

and you will see the M code UI generated below, you can further automate this by replacing the hard-coded names or even wrapping it into a function if you want. 

let
    Source = Data,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Source.Name] = "SalesLTAddress.csv")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"AddressID", "AddressLine1", "AddressLine2"})
in
    #"Removed Other Columns"

[MarkFlrz]

Hi@MasonMA,

With this option I would need to add each one of the file names? 
If so is there a code that I can use to pull all files contained in the folder source?

[raisurrahman]

Could you please share some sample data along with the required format as a snapshot? This would be helpful for everyone.

[MasonMA · Accepted solution]

Hi, 

No, you don’t need to add every file name.
You either keep only columns once in the combined query (applies to all files). Or, if need per-file logic filter on Source.Name patterns instead of listing every single file.

Also instead of “Combine & Transform” you can also manually control it with a few lines of sample M code like below

let
    // Step 1: Load all files in the folder
    Source = Folder.Files("C:\YourFolderPath"),

    // Step 2: Filter only Excel files (if needed)
    ExcelFiles = Table.SelectRows(Source, each [Extension] = ".xlsx"),

    // Step 3: Extract tables/sheets from each file
    GetTables = List.Transform(ExcelFiles[Content], each Excel.Workbook(_, true)),

    // Step 4: Append everything together
    Combined = Table.Combine(GetTables),

    // Step 5: Keep only the columns you want
    Final = Table.SelectColumns(Combined, {"AddressID", "AddressLine1", "AddressLine2"})
in
    Final