Efficient append of Excels & sheets

[mlsx4]

Hello everyone!

I have several Excels (one per year) with different sheets, but same structure (one per center). At the end I need all of them to be combined in a unique table to manage properly all information. I have done it but in a very inefficient way, since Power BI shows a lot of times OutofMemory Exception. 

Excel 2023: has 29 sheets with exactly same structure.

Excel 2024: has 29 sheets with exactly same structure.

The thing is that I need to keep year and center code (which is in the name of the sheet) to be able to differentiate data.

Thanks for your help

[VahidDM]

Hey @mlsx4 

have you seen this:

https://m.youtube.com/watch?v=SzOk1xjx2z4

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[mlsx4]

Hey @VahidDM 

I need to take a deeper look but... I think your link will only solve the problem of appending data inside an Excel, but how to merge all excels in the folder?

[VahidDM]

Check these 2:

https://m.youtube.com/watch?v=Nbhd0B5ldJE

https://m.youtube.com/watch?v=nPlrQUbEn4o

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[mlsx4]

Hi @VahidDM 

Thank you for your effort but all these links has only one of the conditions. I mean combine different Excels workbooks (with only one sheet) or combine multiple sheets (with only one Excel). I need to do both...

[AlienSx]

Hello, @mlsx4 assumption: the only transformation you need is to promote headers of the tables. Keep an eye on your headers. If "Item" is one of the column names then rename a column with sheet name before expand step (now it's called "Item").

let
    Source = Folder.Files(full_path_to_your_folder)[Content],
    all_data = Table.Combine(List.Transform(Source, Excel.Workbook)),
    sheets = Table.SelectRows(all_data, each [Kind] = "Sheet")[[Item], [Data]],
    promote = Table.TransformColumns(sheets, {"Data", Table.PromoteHeaders}),
    headers = Table.ColumnNames(promote{0}[Data]),
    expand = Table.ExpandTableColumn(promote, "Data", headers)
in
    expand

[mlsx4]

Hi @AlienSx 

It almost works perfectly. My problem is that I'm missing the name of the workbook which is key for me because it contains the year... Is there any workaround?

[AlienSx · Accepted solution]

@mlsx4 sure. Takes some extra efforts. Hope this won't ruin performance.

let
    Source = Folder.Files(path_to_folder)[[Name], [Content]],
    all_data = Table.TransformColumns(Source, {"Content", Excel.Workbook}),
    expand01 = Table.ExpandTableColumn(all_data, "Content", {"Data", "Item", "Kind"}),
    sheets = Table.SelectRows(expand01, each [Kind] = "Sheet")[[Name], [Item], [Data]],
    promote = Table.TransformColumns(sheets, {"Data", Table.PromoteHeaders}),
    rename = Table.RenameColumns(promote,{{"Name", "file_name"}, {"Item", "sheet_name"}}),
    headers = Table.ColumnNames(rename{0}[Data]),
    expand = Table.ExpandTableColumn(rename, "Data", headers)
in
    expand

[mlsx4]

Hello @AlienSx 

Up to now, it's working perfectly and efficiently! Thank you so much.

You're amazing!