Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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!
if there is any other brain who could help me with this it'd be much appreciated! 🙂
maybe anyone else has a clever idea? 🙂
noone - maybe? 😞
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"
@Anonymous
Can you share your Advanced Editor code?
@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.
@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.
@smpa01 thanks for coming back.
sorry, I cannot quite follow ... what am I supposed to do where?
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),
@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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.