Latest Enterprise DNA Initiatives

Combine the data in different tabs of an excel workbook

Hi @Anu,

@Melissa is correct the step #“Expanded Custom1” is causing the code running slow. As you are unpivoting the columns then you should unpivot it before expanding it, in that way it will be faster as we will not have to call the unique column. Also please note if you have too many subfolders in sharepoint then it will be very very slow. So try to have less subfolders in sharepoint.

let
    Source = SharePoint.Files("https://properties.sharepoint.com/", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each [Folder Path] = "https://properties.sharepoint.com/Shared Documents/Finance/New Kariba Bank Balances/"),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content"}),
    #"GetWbContent" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content],false)),
    #"Removed Other Columns1" = Table.SelectColumns(#"GetWbContent",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each ([Custom.Kind] = "Sheet") and ([Custom.Item] <> "Template")),
    #"Removed Other Columns2" = Table.SelectColumns(#"Filtered Rows1",{"Custom.Name", "Custom.Data"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns2",{{"Custom.Name", "Name"}, {"Custom.Data", "Data"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "PromoteHeaders", each Table.PromoteHeaders([Data], [PromoteAllScalars=true])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each Table.Buffer( Table.RemoveColumns([PromoteHeaders], List.Select(Table.ColumnNames([PromoteHeaders]), each Text.StartsWith(_,"Column"))))),
    #"Removed Other Columns3" = Table.SelectColumns(#"Added Custom1",{"Name", "Custom"}),
    ColumnName = FxDistinctColumnNames(#"Removed Other Columns1",1),
    #"Converted to Table" = Table.FromList(ColumnName, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Filtered Rows3" = Table.SelectRows(#"Converted to Table", each not Text.EndsWith([Column1], "2020")),
    PivotColumnName = #"Filtered Rows3"[Column1],
    UnpivotTable = Table.AddColumn(#"Removed Other Columns1", "Custom.1", each Table.UnpivotOtherColumns([Custom], PivotColumnName, "Attribute", "Value")),
    #"Removed Columns" = Table.RemoveColumns(UnpivotTable,{"Custom"}),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Columns", "Custom.1", {"Bank", "Currency", "Type", "Account Type 2", "Bot Remarks", "Comments", "Attribute", "Value"}, {"Bank", "Currency", "Type", "Account Type 2", "Bot Remarks", "Comments", "Attribute", "Value"})
in
    #"Expanded Custom.1"
2 Likes

Worth exploring…

This is much faster!