Append new Excel table into dataset without loading again old data?

[minhvuong93]

Hi everyone,

I've read a few topics on how to append data in PBI but still find it very slow/

So I have a large number of excel files like below and I imported into PBI via folder import (code as below).

EAch month when I have a new file (which basically is monthly volume sales), I tried to put in the same folder and refresh the query. But I see it loaded again from Jan'17 file until the latest month data...which is an EXTREMELY SLOW process...and sometimes it just froze like forever....

I have pasted my query below to get the data from folder.

My query:

let
    Source = Folder.Files("E:\DMS EMS\SALES BY CUSTOMER SKU\2017\HCMP"),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content],true)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Name", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Name.1", "Name.2", "Name.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name.1", Int64.Type}, {"Name.2", type text}, {"Name.3", type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"Name.3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Name.2", "Location"}}),
    #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Renamed Columns", {{"Name.1", type text}}, "en-GB"), "Name.1", Splitter.SplitTextByPositions({0, 4}, false), {"Name.1.1", "Name.1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Name.1.1", Int64.Type}, {"Name.1.2", Int64.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Name.1.2", "Month"}, {"Name.1.1", "Year"}}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Renamed Columns1", "Custom", {"Name", "Data", "Kind"}, {"Name", "Data", "Kind"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each ([Kind] = "Sheet")),
    #"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows1", "Data", {"LocalSalesRegionLevel4", "LocalSalesRegionLevel4Name", "CustomerCode", "CustomerName", "OutletCode", "OutletName", "SalesRouteCode", "SalesRouteName", "SalesRouteType", "ContactName", "PhoneNumber", "Address1", "Address2", "Address3", "TerritoryL4", "TerritoryL4Name", "OutletLevel2", "Visicooler", "PepsiClub", "AnchorAccount", "CreatedDate", "Level2Code", "Level2Name", "ProductCode", "ProductName", "OrderQuantity", "OrderCount", "VisitPlan"}, {"LocalSalesRegionLevel4", "LocalSalesRegionLevel4Name", "CustomerCode", "CustomerName", "OutletCode", "OutletName", "SalesRouteCode", "SalesRouteName", "SalesRouteType", "ContactName", "PhoneNumber", "Address1", "Address2", "Address3", "TerritoryL4", "TerritoryL4Name", "OutletLevel2", "Visicooler", "PepsiClub", "AnchorAccount", "CreatedDate", "Level2Code", "Level2Name", "ProductCode", "ProductName", "OrderQuantity", "OrderCount", "VisitPlan"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Data", each ([Kind] = "Sheet")),
    #"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows",{"Content", "Kind", "Name", "LocalSalesRegionLevel4"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns2",{{"CreatedDate", type datetime}, {"OrderQuantity", type number}, {"OrderCount", type number}})
in
    #"Changed Type2"

My excel have table header like below, These stay the same,

but some months there were extra but unnecessary headers. 

LocalSalesRegionLevel4

LocalSalesRegionLevel4Name

CustomerCode

CustomerName

OutletCode

OutletName

OutletGroup

OutletType

SalesRouteCode

SalesRouteName

SalesRouteType

ContactName

PhoneNumber

Address1

Address2

Address3

TerritoryL3

TerritoryL3Name

TerritoryL4

TerritoryL4Name

OutletLevel2

Visicooler

PepsiClub

AnchorAccount

CreatedDate

Level2Code

Level2Name

ProductCode

ProductName

OrderQuantity

OrderCount

VisitPlan

[Anonymous · Accepted solution]

Hi @minhvuong93,

I'd like to suggest you add some filters after get data from folder and before expand records, it will obvious increase the load performance.

>>My excel have table header like below, These stay the same, but some months there were extra but unnecessary headers. 

For this scenario, you can try to use Table.SelectColumns to instead RemoveColumns function to choose which columns you needed.(not need to care about randomly needless columns' name)

BTW, folder connector not support incremental loading/refresh.

Regards,

Xiaoxin Sheng