Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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!

Anonymous
Not applicable

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

Anonymous
Not applicable

maybe anyone else has a clever idea? 🙂

Anonymous
Not applicable

noone - maybe? 😞

Anonymous
Not applicable

@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

@Anonymous 

 

Can you share your Advanced Editor code?

Anonymous
Not applicable

@darentengmfs 

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.

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

@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

@Anonymousone 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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

@smpa01 thanks for coming back.

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

Anonymous
Not applicable

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),

Anonymous
Not applicable

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors