Combining sheets from many excel files with different sheet names, transform then combine

[Bizzay]

Hello everyone,

I'll start by saying that I'm quite new to PowerBI and Power Query and have been researching for a solution to my specific question for a long time but don't have it right.  The closest resource I found and was able to replicate was from the video:

How To Transform Nested Tables & Then Combine Them - Power Query - YouTube

however, I am not familiar with how to modify the M code for my transformations.

The setup:

I have a collection of excel workbooks saved within a main folder.  Each of these workbooks has X number of sheets, but usually one or two that are dates (e.g. May 10, May 11) + two additional tabs with fixed names which I do not want to use for my query.

I have used the Transform Sample File method that has allowed me to then filter the records (tables) and omit the sheet names I want to exclude, and that leaves me with a list (Table?) of only the records (Tables?) I need to transform then combine.  My issue is that I need to perform a number of transformations to each Table, and then combine all the lines from all tables into a single table, but am missing some fundamental understanding in how to do this.

My transformation for each of the individual tables includes the following steps (which I have created in a Test Query named "2023 DDR (2)" but this only works for having already drilled down into a specific table:

My issue is that I don't know how to apply all of the above steps to each table before combining them.

I have tried to start by applying the first transformation step (Add Column named "DDRDate" and apply the value from the [Column5]{3} of that table to all records in that table) to the main query, which now looks like this:

let
Source = Folder.Files("R:\REDACTED"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", 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"}),
CombinedTables = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Removed Errors" = Table.RemoveRowsWithErrors(CombinedTables, {"Source.Name", "Name", "Data", "Item", "Kind", "Hidden"}),
TransformationFunction = Table.TransformColumns (#"Removed Errors", {"Data", each Table.AddColumn(_, "DDRDate", each _[Column5]{3})})
in
TransformationFunction

 I'm having issues understanding how to refer to each table and suspect that I've got the following portion incorrect:

{"Data", each Table.AddColumn(_, "DDRDate", each _[Column5]{3})

The formula does correctly create a column named DDRDate in each table, but is showing me an error value on each line instead of populating with the correct value.

This is only the first transformation of several steps but could work through the rest if I can figure this out.   Any help would be greatly appreciated.  Thanks in advance!

[Bizzay]

Hi @Anonymous ,

That worked like an absolute charm!  Thanks so much for taking the time to help.

[Anonymous · Accepted solution]

Hi @Bizzay ,

What kind of error is returned? Is there a value for [Column5]{3}'s in each table?
What about adding a try like this to catch errors?

    TransformDDR = (t as table) as table =>
        let
            AddedDDRDate = Table.AddColumn(t, "DDRDate", each try t[Column5]{3} otherwise null),
            Result = AddedDDRDate
        in
            Result,
    TransformationFunction = Table.TransformColumns(#"Removed Errors", {"Data", each TransformDDR(_)})

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group