Forum Discussion

mlsx4's avatar
mlsx4
Icon for Memorable Member rankMemorable Member
3 years ago
Solved

Efficient append of Excels & sheets

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

Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    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

     

8 Replies