Alternative to Consolidate Function

[Gpaul]

I have used the Consolidate function to solve this problem but I didn't like the end result.

Anyone have any additional solutions to summarizing 39 Identical templates that are in separate workbooks into a separate master summary workbook?

There are 4 sections on each workbook (which represent a division) that look like the below format. I would like to find a way to get this summarized in a separate workbook in the same format. One note there may be additional workbooks in the future so the solution needs to be able to be updated to include the new templates.

 

[C Moore]

Power BI could accept a folder of different books and combine them. You'd have to set it up to export in a way you can place in a new book manually to avoid having to buy a license.

 

[Gpaul]

Thanks for the response, I have a PowerBI license but the issue I am running into is the format has to be exactly as I posted which seems to be an issue. Also, I need a workbook that updates dynamically as the workbooks are periodically updated through the month. I have for the time being just linked to each of the workbooks but long term would like to find a better solution that is not such a pain to manage.

 

[C Moore]

I don't know your abilities and I'm a googling hack so take what you will from this.
Combining files that aren't all the same requires more work with Transforming Files. I have to liberally use a created Index column with SelectRows and Table.Skip to 'line up' all the templates that are supposed to match. For example 'Code' is the row I want to make headers. Code is always in [Column5] and supposed to be on line 3, but extra rows were inserted that moves it to 5.

#"Header Row To Use" = Table.SelectRows(#"Added Index", each ([Column5] = "Code"))[Index]{0},
#"Skip to Header Row" = Table.Skip(#"Added Index",#"Header Row To Use"),

[Index]{0} returns an integer so skip moves it down to adjust my headers.

 

[Gpaul]

I don't know your abilities and I'm a googling hack so take what you will from this.
Combining files that aren't all the same requires more work with Transforming Files. I have to liberally use a created Index column with SelectRows and Table.Skip to 'line up' all the templates that are supposed to match. For example 'Code' is the row I want to make headers. Code is always in [Column5] and supposed to be on line 3, but extra rows were inserted that moves it to 5.

#"Header Row To Use" = Table.SelectRows(#"Added Index", each ([Column5] = "Code"))[Index]{0},
#"Skip to Header Row" = Table.Skip(#"Added Index",#"Header Row To Use"),

[Index]{0} returns an integer so skip moves it down to adjust my headers.

I am not sure my BI skills are up to the task but I will give it a look. Thanks for taking the time to try and solve it for me!