How to Run a power query efficiently to use less memory in refresh

[pranay_singh]

I have been facing issues with running my query efficiently online. The query below is using an excel file pulled from a SharePoint site daily. There are some calculations involved to get the network days between two columns for which I use a network days function.

The issue is that the number of rows keep on increasing everyday and calculation for each row increases which is causing the refresh to fail.

I have been reading online to use Table.Buffer or List.Buffer to make is more faster and less memory-consuming. Have not had luck in using it. 

How to best handle the query or re write to make it faster?

let
    Source = Table.Buffer(SharePoint.Contents("https://sgsintl.sharepoint.com/sites/Reports/", [ApiVersion = 15])),
    Highlevel = Source,
    Root = Highlevel{[Name="Shared Documents"]}[Content],
    Client = Root{[Name="Client"]}[Content],
    Approvers1 = Client{[Name="Approvers"]}[Content],
    #"Sorted Rows" = Table.Sort(Approvers1,{{"Date created", Order.Descending}}),
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each not Text.Contains([Name], "-$")),
    #"Kept First Rows" = Table.FirstN(#"Filtered Rows",1),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Kept First Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Number (Project)", type text}, {"Name (Project)", type text}, {"Number (SKU)", type text}, {"Name (SKU)", type text}, {"Parent SKU number (SKU)", type text}, {"Parent SKU name (SKU)", type text}, {"Supplier Name (Project)", type text}, {"Target Approval to Print Date (Project)", type datetime}, {"Creation Date (Project)", type datetime}, {"Creation Date (SKU)", type datetime}, {"Creator (Project)", type text}, {"Creator (SKU)", type text}, {"Component(s) Type (SKU)", type text}, {"Divison (Project)", type text}, {"Dual Merchandising (SKU)", type text},  type text}, {"Imagery needed? (Project)", type text}, {"Marks PM (Project)", type text}, {"Vendor Execution Coordinator (Project)", type text}, {"Packaging Services & Traffic (Project)", type text}, {"Printer (Dropdown) (SKU)", type any}, {"Product Category (SKU)", type text}, {"Purpose (Project)", type text}, {"Label Design Type (Project)", type text}, {"Client or Member Brand (Project)", type text}, {"Sub Brand (Project)", type text}, {"Substrate (SKU)", type any}, {"SAP Retail Material Number (SKU)", Int64.Type}, {"SKU template (SKU)", type text}, {"Trademarking Info (Project)", type text}, {"Production Date (SKU)", type datetime}, {"Third Party Certifications or Partnerships (SKU)", type any}, {"Number of UPC's (Project)", Int64.Type}, {"Status (SKU)", type text}, {"Status (Document)", type text}, {"Name (Document)", type text}, {"Type (Document)", type text}, {"Version (Document)", type text}, {"Production start date (Phase)", type datetime}, {"Upload date (Document)", type datetime}, {"Upload user name (Document)", type text}, {"Approval date (Document)", type datetime}, {"Approval start date (Approver)", type datetime}, {"Name (Approval group)", type text}, {"User (Approver)", type text}, {"User approval date (Approver)", type datetime}, {"User approval status (Approver)", type text}, {"Rejection reasons (Approver)", type any}}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Changed Type", "Text After Delimiter", each Text.AfterDelimiter([#"Version (Document)"], " "), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Text After Delimiter",{{"Text After Delimiter", "Version"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Project Web Link", each "https://mdb-indep6.mediabox.com/Mediabox-Independence/Base/webpermalinks?MBI_datasource=&project_num="&[#"Number (Project)"]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Reference Web Link", each "https://mdb-indep6.mediabox.com/Mediabox-Independence/Base/webpermalinks?MBI_datasource=&ref_id="&[#"Number (SKU)"]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Version", type number}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type2", "Days to document upload", each NetworkDays([#"Production start date (Phase)"] as datetime, [#"Upload date (Document)"] as datetime, Table.Column(#"Holidays List","US Holidays") as list)),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Days for document approval", each if [#"Status (Document)"] = "Approved" or [#"Status (Document)"] = "Amendments requested" then NetworkDays ([#"Upload date (Document)"] as datetime, [#"Approval date (Document)"] as datetime, Table.Column(#"Holidays List", "US Holidays") as list) else null),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Days for user approval", each if [#"User approval status (Approver)"] = "Approved" or [#"User approval status (Approver)"] = "Amendments requested" then NetworkDays ([#"Approval start date (Approver)"] as datetime, [#"User approval date (Approver)"] as datetime, Table.Column(#"Holidays List", "US Holidays") as list) else null),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Days for document approval from component creation", each if [#"Status (Document)"] = "Approved" or [#"Status (Document)"] = "Amendments requested" then NetworkDays ([#"Creation Date (SKU)"] as datetime, [#"Approval date (Document)"] as datetime, Table.Column(#"Holidays List", "US Holidays") as list) else null),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom5",{{"Days for document approval from component creation", type number}, {"Days for user approval", type number}, {"Days for document approval", type number}, {"Days to document upload", type number}, {"SAP Retail Material Number (SKU)", type text}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type3", {{"SAP Retail Material Number (SKU)", null}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Errors", {{"Rejection reasons (Approver)", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Rejection reasons (Approver)"),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Rejection reasons (Approver)", type text}})
in
    #"Changed Type4"

[lbendlin]

Use the Query Diagnostics to identify the most costly steps.  Things like 

Table.Sort

are deadly, and most likely not required.

Since you don't show the code for NetworkDays we cannot judge the influence. Please note there is a native DAX function for that.