Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |