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
