cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heri
Frequent Visitor

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 15
heri
Frequent Visitor

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!

heri
Frequent Visitor

if there is any other brain who could help me with this it'd be much appreciated! 🙂

heri
Frequent Visitor

maybe anyone else has a clever idea? 🙂

heri
Frequent Visitor

noone - maybe? 😞

heri
Frequent Visitor

@darentengmfs 

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"
darentengmfs
Post Prodigy
Post Prodigy

@heri 

 

Can you share your Advanced Editor code?

@darentengmfs 

any idea ...? please let me know if you need to know more!

@heriI 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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


heri
Frequent Visitor

@smpa01 thank you for supporting!

 

#"Removed Columns" has 39312 rows,

#"Expanded Sheet2 has 275184 rows. 

 

the right row count should be 39312 rows, currently is 275184 rows

@herione of the possibilites might be you have duplicate value on the right table on the join key column.

 Can you remove duplicates from the join key column from the right table and trythe join again. It should match 1-1 and you would have the same number of rows after join.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


heri
Frequent Visitor

@smpa01 thanks for coming back.

sorry, I cannot quite follow ... what am I supposed to do where?

heri
Frequent Visitor

hey all, unfortunately I still can't solve this x.x

Did you try the buffer? 

 

Replace your merge step with this and see if it still loads multiple times.:

 

#"Merged Queries" = Table.NestedJoin(Table.Buffer(#"Removed Columns"), {"UsageWeekNo"}, Table.Buffer(Sheet2), {"yrmth"}, "Sheet2", JoinKind.LeftOuter),

heri
Frequent Visitor

@justinh thank you for your reply! sorry, i didn't see your comment before.

 

i am still quite new to pbi so thanks for the easy-to-grasp explanation.

unfortunately, the result stays the same 😕

fyr:

 

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"}, Table.Buffer(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"

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors