Incremental Refresh of Sharepoint Folder Files in Power BI

[hasharma19]

Hi All,

I want to implement incremental refresh of dataset which is linked from Sharepoint folder files.

There are 7M rows and whereever i tried to refresh data set in power bi desktop it almots take 15 hrs or more then that, infact it disconect in between stating time out. I used to store my files on weekly basis wherein i got data of 75K row with 140 columns in sheet(Screenshort attached). Tried to set incremental refresh using parameter but its not getting implemented since data not getting filtered as per parameter(seems due to hugh data).

below is simpe query of my table.

let
Source = SharePoint.Files("https://****.sharepoint.com/teams/INQCCTeam", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx") and ([Folder Path] = "https://****.sharepoint.com/teams/INQCCTeam/Shared Documents/General/DE Exc Undel PBI Reporting Files/")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Shipment SID / Pcs Rec Key", Int64.Type}, {"Waybill Number", Int64.Type}, {"Shp Rec Key", Int64.Type}, {"Piece ID", type any}, {"Product Group", type text}, {"OPS Product Group", type text}, {"Product Code", type text}, {"Product Content Code", type text}, {"Product Description", type text}, {"Network Charge Code", type text}, {"Direct Injection", type text}, {"Shp Manif Dest Stn", type text}, {"Shp Manif Wt(Kg)", type number}, {"Shp Manif Vol Weight", type number}, {"Shp Calc Pcs", Int64.Type}, {"Shp/Pce Calc Actual Weight", type number}, {"Shp/Pce Calc Vol Weight", type number}, {"USD Value", type number}, {"Value Class", type text}, {"Weight Type", type text}, {"Weight Group", type text}, {"Origin Super Region Name", type text}, {"Origin Region Name", type text}, {"Origin Sub Region Name", type text}, {"Origin Region Area Name", type text}, {"Origin Cluster Name", type text}, {"Origin Country Code", type text}, {"Origin Country Region Name", type text}, {"Origin Country Area Name", type text}, {"Origin Station Code", type text}, {"Origin Facility Code", type text}, {"Origin Route", type text}, {"Origin Postcode", type text}, {"Dest Super Region Name", type text}, {"Dest Region Name", type text}, {"Dest Sub Region Name", type text}, {"Dest Region Area Name", type text}, {"Dest Cluster Name", type text}, {"Dest Country Code", type text}, {"Dest Country Region Name", type text}, {"Dest Country Area Name", type text}, {"Dest Station Code", type text}, {"Dest Facility Code", type text}, {"Dest Route", type text}, {"Dest Route Cycle", type text}, {"Dest Zip", Int64.Type}, {"Exist Dest Zip", Int64.Type}, {"Country Lane", type text}, {"Station Lane", type text}, {"Customer Acct", type text}, {"Shpr Name", type text}, {"Rcvr Name", type text}, {"Rcvr Address", type text}, {"Rcvr City", type text}, {"Start Clk Checkpoint", type text}, {"Start Clk Date", type date}, {"Start Clk Time", type time}, {"Start Clk DOW", type text}, {"PU Cut Tm", Int64.Type}, {"PU Cut Flag", type text}, {"TTS PU Cutoff Rank", Int64.Type}, {"TTS Del Service Level", Int64.Type}, {"TTS Tgt Days", Int64.Type}, {"Addl PU Days NC", Int64.Type}, {"Addl PU Days SC", Int64.Type}, {"Addl Del Days NC", Int64.Type}, {"Addl Del Days SC", Int64.Type}, {"Temp Stop Clk Days", Int64.Type}, {"Act TT Days NC", Int64.Type}, {"Act TT Days SC", Int64.Type}, {"EDD NC", type date}, {"EDD SC", type date}, {"Target Del Tm NC", Int64.Type}, {"Target Del Tm SC", Int64.Type}, {"NC Ontime", Int64.Type}, {"SC Ontime", Int64.Type}, {"ERDF NC Date", type date}, {"ERDF SC Date", type date}, {"Met RDD NC?", type text}, {"Met RDD SC?", type text}, {"NC Stop Clk Checkpoint", type text}, {"NC Stop Clk Date", type date}, {"NC Stop Clk Time", type time}, {"NC Stop Clk Remark", type text}, {"SC Stop Clk Checkpoint", type text}, {"SC Stop Clk Date", type date}, {"SC Stop Clk Time", type time}, {"SC Stop Clk Stn", type text}, {"SC Stop Clk Fac", type text}, {"SC Stop Clk Route", type text}, {"Arrival Checkpoint", type text}, {"Arrival Date", type date}, {"Arrival Time", type time}, {"Arrival Ctry", type text}, {"Arrival Stn", type text}, {"Arrival Fac", type text}, {"Arrival Route", type text}, {"Arrival Timeband", type text}, {"WC Date", type date}, {"WC Time", type time}, {"WC Route", type text}, {"Del Exc Checkpoint", type text}, {"Del Exc Date", type date}, {"Del Exc Time", type time}, {"Del Exc Ctry", type text}, {"Del Exc Stn", type text}, {"Del Exc Fac", type text}, {"Del Exc Route", type text}, {"Del Exc Route Cycle", type text}, {"Del Exc Route Type", type text}, {"Del Exc Aging Category", type text}, {"Del Exc Aging", type text}, {"Del Exc Remark ", type text}, {"Has ST ODD", Int64.Type}, {"Delivery Timeband", type text}, {"Last Physical Checkpoint", type text}, {"Last Physical Ckpt Date", type date}, {"Last Physical Ckpt Time", type time}, {"Last Physical Ckpt Ctry", type text}, {"Last Physical Ckpt Stn", type text}, {"Last Physical Ckpt Fac", type text}, {"Last Physical Ckpt Route", type text}, {"Elapsed TT NC", type number}, {"Elapsed TT SC", type number}, {"PAT SC Resp Region Name", type text}, {"PAT SC Resp Country Code", type text}, {"PAT SC Resp Country Name", type text}, {"PAT SC Resp Station Code", type text}, {"PAT SC Resp Facility Code", type text}, {"PAT SC Resp Route", type text}, {"PAT SC Failure Allocation", type text}, {"Contents", type text}, {"Payer Account", type text}, {"PU Route", type text}, {"Column135", type any}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Source.Name", "Waybill Number", "Product Group", "OPS Product Group", "Product Code", "Product Content Code", "Product Description", "Network Charge Code", "Direct Injection", "Shp Manif Dest Stn", "Shp Manif Wt(Kg)", "Shp Manif Vol Weight", "Shp Calc Pcs", "Shp/Pce Calc Actual Weight", "Shp/Pce Calc Vol Weight", "Origin Super Region Name", "Origin Region Name", "Origin Sub Region Name", "Origin Region Area Name", "Origin Cluster Name", "Origin Country Code", "Origin Country Region Name", "Origin Country Area Name", "Origin Station Code", "Origin Facility Code", "Origin Route", "Origin Postcode", "Dest Super Region Name", "Dest Region Name", "Dest Sub Region Name", "Dest Region Area Name", "Dest Cluster Name", "Dest Country Code", "Dest Country Region Name", "Dest Country Area Name", "Dest Station Code", "Dest Facility Code", "Dest Route", "Dest Route Cycle", "Dest Zip", "Exist Dest Zip", "Country Lane", "Station Lane", "Customer Acct", "Shpr Name", "Rcvr Name", "Rcvr Address", "Rcvr City", "Start Clk Checkpoint", "Start Clk Date", "Start Clk Time", "Start Clk DOW", "PU Cut Tm", "PU Cut Flag", "TTS PU Cutoff Rank", "TTS Del Service Level", "TTS Tgt Days", "Addl PU Days NC", "Addl PU Days SC", "Addl Del Days NC", "Addl Del Days SC", "Temp Stop Clk Days", "Act TT Days NC", "Act TT Days SC", "EDD NC", "EDD SC", "Target Del Tm NC", "Target Del Tm SC", "NC Ontime", "SC Ontime", "ERDF NC Date", "ERDF SC Date", "Met RDD NC?", "Met RDD SC?", "NC Stop Clk Checkpoint", "NC Stop Clk Date", "NC Stop Clk Time", "NC Stop Clk Remark", "SC Stop Clk Checkpoint", "SC Stop Clk Date", "SC Stop Clk Time", "SC Stop Clk Stn", "SC Stop Clk Fac", "SC Stop Clk Route", "Arrival Checkpoint", "Arrival Date", "Arrival Time", "Arrival Ctry", "Arrival Stn", "Arrival Fac", "Arrival Route", "Arrival Timeband", "WC Date", "WC Time", "WC Route", "Del Exc Checkpoint", "Del Exc Date", "Del Exc Time", "Del Exc Ctry", "Del Exc Stn", "Del Exc Fac", "Del Exc Route", "Del Exc Route Cycle", "Del Exc Route Type", "Del Exc Aging Category", "Del Exc Aging", "Del Exc Remark ", "Has ST ODD", "Delivery Timeband", "Last Physical Checkpoint", "Last Physical Ckpt Date", "Last Physical Ckpt Time", "Last Physical Ckpt Ctry", "Last Physical Ckpt Stn", "Last Physical Ckpt Fac", "Last Physical Ckpt Route", "Elapsed TT NC", "Elapsed TT SC", "PAT SC Resp Region Name", "PAT SC Resp Country Code", "PAT SC Resp Country Name", "PAT SC Resp Station Code", "PAT SC Resp Facility Code", "PAT SC Resp Route", "PAT SC Failure Allocation", "Payer Account", "PU Route"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Facility", each [Dest Station Code]&"-"&[Dest Facility Code]),
#"Removed Other Columns2" = Table.SelectColumns(#"Added Custom",{"Source.Name", "Waybill Number", "Product Code", "Shp Calc Pcs", "Shp/Pce Calc Actual Weight", "Shp/Pce Calc Vol Weight", "Origin Country Code", "Origin Station Code", "Origin Facility Code", "Origin Postcode", "Dest Region Name", "Dest Country Code", "Dest Station Code", "Dest Facility Code", "Dest Route", "Dest Route Cycle", "Dest Zip", "Exist Dest Zip", "Customer Acct", "Shpr Name", "Rcvr Name", "Rcvr City", "Act TT Days NC", "Act TT Days SC", "EDD NC", "EDD SC", "Target Del Tm NC", "Target Del Tm SC", "NC Ontime", "SC Ontime", "ERDF NC Date", "ERDF SC Date", "Met RDD NC?", "Met RDD SC?", "Arrival Date", "Arrival Time", "Arrival Ctry", "Arrival Stn", "Arrival Fac", "Arrival Route", "Arrival Timeband", "WC Date", "WC Time", "WC Route", "Del Exc Checkpoint", "Del Exc Date", "Del Exc Time", "Del Exc Ctry", "Del Exc Stn", "Del Exc Fac", "Del Exc Route", "Del Exc Route Cycle", "Del Exc Route Type", "Del Exc Aging Category", "Del Exc Aging", "Del Exc Remark ", "Has ST ODD", "Delivery Timeband", "Last Physical Checkpoint", "Last Physical Ckpt Date", "Last Physical Ckpt Time", "Last Physical Ckpt Ctry", "Last Physical Ckpt Stn", "Last Physical Ckpt Fac", "Last Physical Ckpt Route", "Elapsed TT NC", "Elapsed TT SC", "Facility"})
in
#"Removed Other Columns2"