Forum Discussion
Improve performance to refresh data power query - multiples files
Hi guys,
I have an import to power bi, the folder that contains multiples excel files, in this folder has 300 files, 400 MB the total size. The transformantion is very simple, but this is taking a lot of time to refresh. Any suggestion to improve the performance? In my dashboard I need to show sum ( cash + stock ) by month for each day of month year.
The excel is one by day for example: Excel1_01012020; Excel2_02012020 (ddmmyyyy)
1- Combine and edit method script
let
Source = Excel.Workbook(#"Sample File Parameter1", null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Kept First Rows" = Table.FirstN(#"Promoted Headers",each [Clients] <> null),
#"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"Clients", "Stock", "Cash"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Stock", Int64.Type}, {"Cash", Int64.Type}, {"Clients", type text}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type",1)
in
#"Removed Bottom Rows"
2-Other queries script
let
Source = Folder.Files("C:\Users\User01\Desktop\Folder1\New Folder Performance"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from New Folder Performance", each #"Transform File from New Folder Performance"([Content])),
#"Removed Other Columns" = Table.SelectColumns(#"Invoke Custom Function1",{"Name", "Transform File from New Folder Performance"}),
#"Expanded Transform File from New Folder Performance" = Table.ExpandTableColumn(#"Removed Other Columns", "Transform File from New Folder Performance", {"Clients", "Stock", "Cash"}, {"Clients", "Stock", "Cash"}),
#"Extracted Text Between Delimiters" = Table.TransformColumns(#"Expanded Transform File from New Folder Performance", {{"Name", each Text.BetweenDelimiters(_, "_", "_"), type text}}),
#"Inserted Literal" = Table.AddColumn(#"Extracted Text Between Delimiters", "Date", each "01/01/2020", type text),
#"Changed Type" = Table.TransformColumnTypes(#"Inserted Literal",{{"Date", type date}}),
#"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Merged Column" = Table.AddColumn(#"Inserted Year", "Month", each Text.Combine({[Month Name], Text.From([Year], "pt-PT")}, "-"), type text),
#"Inserted Day" = Table.AddColumn(#"Inserted Merged Column", "Day", each Date.Day([Date]), Int64.Type)
in
#"Inserted Day"
I'm trying to undestand how can improve the refresh to stop taking hours to load data.
Table.buffer? Where use this? Another way to import the data? I appreciate your help
Hello twister8889
then your problem is not Power Query and your code but your infrastructure, and on this regard I cannot help you. You have to talk to your administrators.
Something about your code... I cannot see that you are creating your date-columns out of the filename but you create a static date column by using this syntax
#"Inserted Literal" = Table.AddColumn(#"Extracted Text Between Delimiters", "Date", each "01/01/2020", type text),You add a new column named "date" and adding a text-value. Then you are using the date-column for creating all your other columns like year etc. As edhans is also mentioning is that in a datamodel is better to avoid redundant data and this means that is better to create a date-dimension with all your data (like year) and then connect to your fact table through your date-column.
About your performance you can try this code... maybe it's faster
let Source = Folder.Files("C:\Users\User01\Desktop\Folder1\New Folder Performance"), #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from New Folder Performance", each #"Transform File from New Folder Performance"([Content])), #"Removed Other Columns" = Table.SelectColumns(#"Invoke Custom Function1",{"Name", "Transform File from New Folder Performance"}), #"Expanded Transform File from New Folder Performance" = Table.Buffer(Table.ExpandTableColumn(#"Removed Other Columns", "Transform File from New Folder Performance", {"Clients", "Stock", "Cash"}, {"Clients", "Stock", "Cash"})), #"Extracted Text Between Delimiters" = Table.TransformColumns(#"Expanded Transform File from New Folder Performance", {{"Name", each Text.BetweenDelimiters(_, "_", "_"), type text}}), #"Inserted Literal" = Table.AddColumn(#"Extracted Text Between Delimiters", "Date", each "01/01/2020", type text), #"Changed Type" = Table.TransformColumnTypes(#"Inserted Literal",{{"Date", type date}}), #"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([Date]), type text), #"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Date]), Int64.Type), #"Inserted Merged Column" = Table.AddColumn(#"Inserted Year", "Month", each Text.Combine({[Month Name], Text.From([Year], "pt-PT")}, "-"), type text), #"Inserted Day" = Table.AddColumn(#"Inserted Merged Column", "Day", each Date.Day([Date]), Int64.Type) in #"Inserted Day"
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
6 Replies
Hello twister8889
cannot see really a problem with your code. However you can try this. Maybe it helps. Beforehand some other question. You are extracting the date of your file names but then adding a new column with "01/01/2020" and calculating out from your static date the columns Year, Monthname etc. I cannot see any reason for this. You could do all this steps within 2 steps.
Now here the code.. maybe its faster
let Source = Folder.Files("C:\Users\User01\Desktop\Folder1\New Folder Performance"), #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from New Folder Performance", each #"Transform File from New Folder Performance"([Content])), #"Removed Other Columns" = Table.Buffer(Table.SelectColumns(#"Invoke Custom Function1",{"Name", "Transform File from New Folder Performance"})), #"Expanded Transform File from New Folder Performance" = Table.ExpandTableColumn(#"Removed Other Columns", "Transform File from New Folder Performance", {"Clients", "Stock", "Cash"}, {"Clients", "Stock", "Cash"}), #"Extracted Text Between Delimiters" = Table.TransformColumns(#"Expanded Transform File from New Folder Performance", {{"Name", each Text.BetweenDelimiters(_, "_", "_"), type text}}), #"Inserted Literal" = Table.AddColumn(#"Extracted Text Between Delimiters", "Date", each "01/01/2020", type text), #"Changed Type" = Table.TransformColumnTypes(#"Inserted Literal",{{"Date", type date}}), #"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([Date]), type text), #"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Date]), Int64.Type), #"Inserted Merged Column" = Table.AddColumn(#"Inserted Year", "Month", each Text.Combine({[Month Name], Text.From([Year], "pt-PT")}, "-"), type text), #"Inserted Day" = Table.AddColumn(#"Inserted Merged Column", "Day", each Date.Day([Date]), Int64.Type) in #"Inserted Day"
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyFirst of all, thank you for your answer...
I need the split date for use in my visualization table, I need to show values by Day, Month Name-Year. So the date is in the file name, I extracted the date and split it. ( make sense now? )
I don't understand how to do this: You could do all these steps within 2 steps
I created this question considering the company environment, I'm using the VPN and the folder with excels is in the shared folder, and this is taking hours to refresh. Now I tried to reproduce the excel structure in my laptop, with a local folder with 400 MB size, each excel file 464 KB and 928 items, and this is taking 1 minute and 20 seconds.My problem is the VPN, network?.... other things are impacting me? Because as you are said, I cannot see another approach to improve the logic or power query code.
Hello twister8889
then your problem is not Power Query and your code but your infrastructure, and on this regard I cannot help you. You have to talk to your administrators.
Something about your code... I cannot see that you are creating your date-columns out of the filename but you create a static date column by using this syntax
#"Inserted Literal" = Table.AddColumn(#"Extracted Text Between Delimiters", "Date", each "01/01/2020", type text),You add a new column named "date" and adding a text-value. Then you are using the date-column for creating all your other columns like year etc. As edhans is also mentioning is that in a datamodel is better to avoid redundant data and this means that is better to create a date-dimension with all your data (like year) and then connect to your fact table through your date-column.
About your performance you can try this code... maybe it's faster
let Source = Folder.Files("C:\Users\User01\Desktop\Folder1\New Folder Performance"), #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from New Folder Performance", each #"Transform File from New Folder Performance"([Content])), #"Removed Other Columns" = Table.SelectColumns(#"Invoke Custom Function1",{"Name", "Transform File from New Folder Performance"}), #"Expanded Transform File from New Folder Performance" = Table.Buffer(Table.ExpandTableColumn(#"Removed Other Columns", "Transform File from New Folder Performance", {"Clients", "Stock", "Cash"}, {"Clients", "Stock", "Cash"})), #"Extracted Text Between Delimiters" = Table.TransformColumns(#"Expanded Transform File from New Folder Performance", {{"Name", each Text.BetweenDelimiters(_, "_", "_"), type text}}), #"Inserted Literal" = Table.AddColumn(#"Extracted Text Between Delimiters", "Date", each "01/01/2020", type text), #"Changed Type" = Table.TransformColumnTypes(#"Inserted Literal",{{"Date", type date}}), #"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([Date]), type text), #"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Date]), Int64.Type), #"Inserted Merged Column" = Table.AddColumn(#"Inserted Year", "Month", each Text.Combine({[Month Name], Text.From([Year], "pt-PT")}, "-"), type text), #"Inserted Day" = Table.AddColumn(#"Inserted Merged Column", "Day", each Date.Day([Date]), Int64.Type) in #"Inserted Day"
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
