Forum Discussion
Improve performance to refresh data power query - multiples files
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
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
Hello Jimmy801
Still, about the column date 01/01/2020. I tried to Extracted Text Between Delimiters _ and _ because the filename is Excel1_01012020_Team. So I tried to use the add column by example, and after I needed to transform this into a date, month, monthname, year, day. However I'm testing now, and I don't have the expected result, maybe I need to do this another way.
I think that the problem is the 'structure' that I'm using VPN, because in my laptop and local folder the refresh it's not so much slow ( minor than 2 minutes )
I understand about the star schema, and about the creation dim date, but this request is to count the rows by excel (date, year, month, and day). Imagining that the power query is ok, I don't know if it's necessary to create the date dim, but I will create it to test in the company environment.