Forum Discussion
Query editor loads same files multiple times
Hey community,
could someone please help me with the following:
I have PowerBI connected to a folder from which it loads all excel files in it.
Up to here no problem, but:
The data editor loads the data multiple times, 7 times, to be exact.
This obviously slows down the programme a lot.
I have tried refreshing, change the source to something else then back, deleting the files from the source folder and adding them again but nothing works.
Anybody an idea?
Thanks!
15 Replies
Anonymous
Can you share your Advanced Editor code?
any idea ...? please let me know if you need to know more!
AnonymousI suspect that the query loaded up duplicate rows in #"Merged Queries". Can you please count the rows in #"Removed Columns" and #"Expanded Sheet2". If there is 1-1 relationship they will return same number of rows, if duplicated will row count increase. Please test it out and let me know.
thank you for coming back! it's below (if blackened my address only)
let
Source = Folder.Files("C:\...\PowerBI\FC"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (5)", each #"Transform File (5)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (5)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (5)", Table.ColumnNames(#"Transform File (5)"(#"Sample File (5)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"SupplierCode", type text}, {"SupplierName", type text}, {"DockCode", type text}, {"TransmissionDate", type date}, {"PartColourCode", type text}, {"PartDesc", type text}, {"KanbanNo", Int64.Type}, {"PartNo", type text}, {"OrderLot", Int64.Type}, {"UsageWeekNo", type text}, {"UsageDate", type date}, {"TotalPCS", Int64.Type}, {"LastManifest", type any}, {"LeftToOrder", type any}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"SupplierCode", "SupplierName", "PartColourCode", "LastManifest", "LeftToOrder"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"UsageWeekNo"}, Sheet2, {"yrmth"}, "Sheet2", JoinKind.LeftOuter),
#"Expanded Sheet2" = Table.ExpandTableColumn(#"Merged Queries", "Sheet2", {"Mondays"}, {"Sheet2.Mondays"}),
#"Removed Blank Rows" = Table.SelectRows(#"Expanded Sheet2", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
#"Removed Blank Rows"I would give a strategically place Table.Buffer a try (sorry if this has already been mentioned):
#"Merged Queries" = Table.NestedJoin(Table.Buffer(#"Removed Columns"), {"UsageWeekNo"}, Sheet2, {"yrmth"}, "Sheet2", JoinKind.LeftOuter),
let Source = Folder.Files("C:\...\PowerBI\FC"), #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (5)", each #"Transform File (5)"([Content])), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (5)"}), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (5)", Table.ColumnNames(#"Transform File (5)"(#"Sample File (5)"))), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"SupplierCode", type text}, {"SupplierName", type text}, {"DockCode", type text}, {"TransmissionDate", type date}, {"PartColourCode", type text}, {"PartDesc", type text}, {"KanbanNo", Int64.Type}, {"PartNo", type text}, {"OrderLot", Int64.Type}, {"UsageWeekNo", type text}, {"UsageDate", type date}, {"TotalPCS", Int64.Type}, {"LastManifest", type any}, {"LeftToOrder", type any}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"SupplierCode", "SupplierName", "PartColourCode", "LastManifest", "LeftToOrder"}), #"Merged Queries" = Table.NestedJoin(Table.Buffer(#"Removed Columns"), {"UsageWeekNo"}, Sheet2, {"yrmth"}, "Sheet2", JoinKind.LeftOuter), #"Expanded Sheet2" = Table.ExpandTableColumn(#"Merged Queries", "Sheet2", {"Mondays"}, {"Sheet2.Mondays"}), #"Removed Blank Rows" = Table.SelectRows(#"Expanded Sheet2", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))) in #"Removed Blank Rows"
I think I have found out WHY the data loads multiple times.
It loads exactly 7 times and only after I merged it with a date table.
Could someone help me how I can prevent this from happening?
So to be exact:
I have a dataset with week data in format yyyy/mm in table one, and I want to add a column with the Mondays of these weeks into the table. (to be able to create a relationship to another file)
What I have done now is create a second table that holds the data yyyy/mm with a column with the Monday of that week in format "dd/mm/yyyy"
When I merge the tables the data in table 1 is loaded 7 times and I think that has to do with the date.
Could someone help me in how I prevent this, please?
Thank you!
