Power Query slows down after merging queries or tables

B

bobby_smith

Board Regular
Joined
Apr 16, 2014
Messages
90
Hi All,

I'm experiencing a problem where power query slowed down after I merged two tables. Since the merge, each time I do a step, it appears its recalculating or refreshing something in the back ground. the refresh stops when it reaches 120MB which takes about 5 to 10 minutes. So basically each time I add a new step I have to wait 5 to 10 minutes before I can move forward.

One table has 700,000 rows X 30 columns and the other table had 150,000 x 30.

It took me almost an entire day to get 3/4 through what is needed for the end result.

The file was working fine up until I did the merge.

I've come across solutions suggesting that creating a primary key may be helpful but I've not seen any instructions on how to do this. I have a unique ID field that I can assign as primary key if needed.

I would really like some assistance with this as it is taking me forever to finish my query.

Thanks.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
When developing the code for your query it is always possible to filter the data in the source queries first to a smaller subset. When it's all working fine delete the filters.

Peter
 
Upvote 0
Her is my code. There are two main queries I'm using Current Year and Prior Year.
At the end, I try to merge Current Year with the Prior Year to get some new fields created in the Current Year file


Prior Year Query code

let
Source = Folder.Files("C:\Users\..........................."),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Prior Year", each #"Transform File from Prior Year"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Prior Year"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Prior Year", Table.ColumnNames(#"Transform File from Prior Year"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Common System Number", Int64.Type}, {"System Number", Int64.Type}, {"Old System Number", type text}, {"Company", type text}, {"Activity Code", type text}, {"G/L Asset Account", Int64.Type}, {"Vendor", type any}, {"Description", type text}, {"Acquisition Date", type date}, {"Placed-in-Service Date", type date}, {"Current Through Date", type date}, {"Disposal Date", type date}, {"Current Remaining Life", Int64.Type}, {"Depreciation Method", type text}, {"Estimated Life", Int64.Type}, {"Acquisition Value", type number}, {"Current Accum", type number}, {"Net Book Value", type number}, {"Depreciation This Run", type number}, {"Location", type text}, {"Project Code", type text}}),
#"Filtered for Active only" = Table.SelectRows(#"Changed Type", each [Activity Code] = "A"),
#"Remeved Dep exp = 0" = Table.SelectRows(#"Filtered for Active only", each [Depreciation This Run] <> 0),
#"Added Custom" = Table.AddColumn(#"Remeved Dep exp = 0", "Period", each "FY 2018"),
#"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Location"}, Location_Table, {"Location"}, "Location_Table", JoinKind.LeftOuter),
#"Expanded Location_Table" = Table.ExpandTableColumn(#"Merged Queries", "Location_Table", {"Type"}, {"Location_Table.Type"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded Location_Table", {"Common System Number"}, #"Current Year", {"Common System Number"}, "Current Year", JoinKind.LeftOuter)
in
#"Merged Queries1"


Current Year Query code

let
Source = Folder.Files("C:\Users\................"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Current Year", each #"Transform File from Current Year"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Current Year"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Current Year", Table.ColumnNames(#"Transform File from Current Year"(#"Sample File (2)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Common System Number", Int64.Type}, {"System Number", Int64.Type}, {"Old System Number", type text}, {"Company", type text}, {"Activity Code", type text}, {"G/L Asset Account", Int64.Type}, {"Vendor", type text}, {"Description", type text}, {"Acquisition Date", type date}, {"Placed-in-Service Date", type date}, {"Current Through Date", type date}, {"Disposal Date", type date}, {"Current Remaining Life", Int64.Type}, {"Depreciation Method", type text}, {"Estimated Life", Int64.Type}, {"Acquisition Value", type number}, {"Current Accum", type number}, {"Net Book Value", type number}, {"Depreciation This Run", type number}, {"Location", type text}, {"Project Code", type text}, {"Reason for Disposal", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Activity Code] = "A" or [Activity Code] = "D"),
#"Custom Column to determine which Disposed items shodul be deleted" = Table.AddColumn(#"Filtered Rows", "Disposed item to delete", each if[Activity Code]="D" and [Disposal Date]< Disposal_Cutoff_Date then "Delete" else "Do not Delete"),
#"Delete some disposed items" = Table.SelectRows(#"Custom Column to determine which Disposed items shodul be deleted", each [Disposed item to delete] <> "Delete"),
#"Merged Queries" = Table.NestedJoin(#"Delete some disposed items", {"Common System Number"}, #"Prior Year Expense", {"Common System Number"}, "Prior Year Expense", JoinKind.LeftOuter),
#"Added Prior Year Expense" = Table.ExpandTableColumn(#"Merged Queries", "Prior Year Expense", {"Depreciation This Run"}, {"Prior Year Expense.Depreciation This Run"}),
#"Added Custom column for Disp items with no PY exp" = Table.AddColumn(#"Added Prior Year Expense", "Disposed items with do exp in PY", each if[Activity Code] = "D" and [Prior Year Expense.Depreciation This Run] = null then "Delete" else "Do not Delete"),
#"Filtered Rows2" = Table.SelectRows(#"Added Custom column for Disp items with no PY exp", each [Disposed items with do exp in PY] = "Do not Delete"),
#"Added Custom1" = Table.AddColumn(#"Filtered Rows2", "Coding", each if[Activity Code]="D" then "Disposals"
else if [Activity Code]="A" and [Depreciation This Run]=0 and [Prior Year Expense.Depreciation This Run]<>0 then "Fully Depreciated"
else if [Activity Code] = "A" and [Acquisition Date]>= Additions_Prior_Year_Date then "Additions"
else if [Activity Code] = "A" and [Acquisition Date] >= Catchup_Depreciation_Date_Start and [Acquisition Date] <= Catchup_Depreciation_Date_End and [#"Placed-in-Service Date"]<catchup_depreciation_date_start then="" "catchup="" depreciation"="" else="" "still="" depreciating"),
#"Added Conditional Column" = Table.AddColumn(#"Added Custom1", "New Depreciation this Run", each if [Activity Code] = "A" then [Depreciation This Run] else 0),
#"Added Custom" = Table.AddColumn(#"Added Conditional Column", "Period", each "FY 2019"),
#"Added Custom2" = Table.AddColumn(#"Added Custom", "Final Delete", each if[Coding] = "Still Depreciating" and [Depreciation This Run]=0 then "Delete" else "Do not Delete"),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom2", each [Final Delete] = "Do not Delete"),
#"Merged Queries1" = Table.NestedJoin(#"Filtered Rows1", {"Location"}, Location_Table, {"Location"}, "Location_Table", JoinKind.LeftOuter),
#"Expanded Location_Table" = Table.ExpandTableColumn(#"Merged Queries1", "Location_Table", {"Type"}, {"Location_Table.Type"})
in
#"Expanded Location_Table"</catchup_depreciation_date_start>
 
Upvote 0
could you edit your post and use CODE tags?

you've 10 minutes from time of your post: 06:43 PM - 06:53 PM
 
Last edited:
Upvote 0
Peter, do you mind expanding a bit on how best to do this? I tried it, however so much information was filtered out that I was not able to verify along the way if my steps were correct as too much info was already filtered out.

Also, when rows or columns are filtered out, does the query reduce in size? example, if my original query is stating 50MB in size when its refreshing, if I filter out the half the rows and column, would the query drop in size say to 25MB.

I've been trying to test this but unable to see how, I only know the size when its taking forever to refresh update and you see how long it ave remaining.
 
Upvote 0
which query is slow? first or second?

you said you add a KEY but I don't see that
 
Last edited:
Upvote 0
How do I use key tags?

The primary key is not in this code. I was trying it after the query kept slowing down but I was not able to save it because of how long it took.
 
Upvote 0
Both Queries are slow. The Current Year was the slowest at first, however when I completed the current year and merged it with Prior year as then needed a field from the Current Year, the Prior Year got really really slow.

Prior Year Query code

let
Code: 
[COLOR=#333333]Source = Folder.Files("C:\Users\..........................."),[/COLOR]
[COLOR=#333333]#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),[/COLOR]
[COLOR=#333333]#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Prior Year", each #"Transform File from Prior Year"([Content])),[/COLOR]
[COLOR=#333333]#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),[/COLOR]
[COLOR=#333333]#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Prior Year"}),[/COLOR]
[COLOR=#333333]#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Prior Year", Table.ColumnNames(#"Transform File from Prior Year"(#"Sample File"))),[/COLOR]
[COLOR=#333333]#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Common System Number", Int64.Type}, {"System Number", Int64.Type}, {"Old System Number", type text}, {"Company", type text}, {"Activity Code", type text}, {"G/L Asset Account", Int64.Type}, {"Vendor", type any}, {"Description", type text}, {"Acquisition Date", type date}, {"Placed-in-Service Date", type date}, {"Current Through Date", type date}, {"Disposal Date", type date}, {"Current Remaining Life", Int64.Type}, {"Depreciation Method", type text}, {"Estimated Life", Int64.Type}, {"Acquisition Value", type number}, {"Current Accum", type number}, {"Net Book Value", type number}, {"Depreciation This Run", type number}, {"Location", type text}, {"Project Code", type text}}),[/COLOR]
[COLOR=#333333]#"Filtered for Active only" = Table.SelectRows(#"Changed Type", each [Activity Code] = "A"),[/COLOR]
[COLOR=#333333]#"Remeved Dep exp = 0" = Table.SelectRows(#"Filtered for Active only", each [Depreciation This Run] <> 0),[/COLOR]
[COLOR=#333333]#"Added Custom" = Table.AddColumn(#"Remeved Dep exp = 0", "Period", each "FY 2018"),[/COLOR]
[COLOR=#333333]#"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Location"}, Location_Table, {"Location"}, "Location_Table", JoinKind.LeftOuter),[/COLOR]
[COLOR=#333333]#"Expanded Location_Table" = Table.ExpandTableColumn(#"Merged Queries", "Location_Table", {"Type"}, {"Location_Table.Type"}),[/COLOR]
[COLOR=#333333]#"Merged Queries1" = Table.NestedJoin(#"Expanded Location_Table", {"Common System Number"}, #"Current Year", {"Common System Number"}, "Current Year", JoinKind.LeftOuter)[/COLOR]
[COLOR=#333333]in[/COLOR]
[COLOR=#333333]#"Merged Queries1"[/COLOR]


Current Year Query code

let
Code: 
[COLOR=#333333]Source = Folder.Files("C:\Users\................"),[/COLOR]
[COLOR=#333333]#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),[/COLOR]
[COLOR=#333333]#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Current Year", each #"Transform File from Current Year"([Content])),[/COLOR]
[COLOR=#333333]#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),[/COLOR]
[COLOR=#333333]#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Current Year"}),[/COLOR]
[COLOR=#333333]#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Current Year", Table.ColumnNames(#"Transform File from Current Year"(#"Sample File (2)"))),[/COLOR]
[COLOR=#333333]#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Common System Number", Int64.Type}, {"System Number", Int64.Type}, {"Old System Number", type text}, {"Company", type text}, {"Activity Code", type text}, {"G/L Asset Account", Int64.Type}, {"Vendor", type text}, {"Description", type text}, {"Acquisition Date", type date}, {"Placed-in-Service Date", type date}, {"Current Through Date", type date}, {"Disposal Date", type date}, {"Current Remaining Life", Int64.Type}, {"Depreciation Method", type text}, {"Estimated Life", Int64.Type}, {"Acquisition Value", type number}, {"Current Accum", type number}, {"Net Book Value", type number}, {"Depreciation This Run", type number}, {"Location", type text}, {"Project Code", type text}, {"Reason for Disposal", type text}}),[/COLOR]
[COLOR=#333333]#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Activity Code] = "A" or [Activity Code] = "D"),[/COLOR]
[COLOR=#333333]#"Custom Column to determine which Disposed items shodul be deleted" = Table.AddColumn(#"Filtered Rows", "Disposed item to delete", each if[Activity Code]="D" and [Disposal Date]< Disposal_Cutoff_Date then "Delete" else "Do not Delete"),[/COLOR]
[COLOR=#333333]#"Delete some disposed items" = Table.SelectRows(#"Custom Column to determine which Disposed items shodul be deleted", each [Disposed item to delete] <> "Delete"),[/COLOR]
[COLOR=#333333]#"Merged Queries" = Table.NestedJoin(#"Delete some disposed items", {"Common System Number"}, #"Prior Year Expense", {"Common System Number"}, "Prior Year Expense", JoinKind.LeftOuter),[/COLOR]
[COLOR=#333333]#"Added Prior Year Expense" = Table.ExpandTableColumn(#"Merged Queries", "Prior Year Expense", {"Depreciation This Run"}, {"Prior Year Expense.Depreciation This Run"}),[/COLOR]
[COLOR=#333333]#"Added Custom column for Disp items with no PY exp" = Table.AddColumn(#"Added Prior Year Expense", "Disposed items with do exp in PY", each if[Activity Code] = "D" and [Prior Year Expense.Depreciation This Run] = null then "Delete" else "Do not Delete"),[/COLOR]
[COLOR=#333333]#"Filtered Rows2" = Table.SelectRows(#"Added Custom column for Disp items with no PY exp", each [Disposed items with do exp in PY] = "Do not Delete"),[/COLOR]
[COLOR=#333333]#"Added Custom1" = Table.AddColumn(#"Filtered Rows2", "Coding", each if[Activity Code]="D" then "Disposals" [/COLOR]
[COLOR=#333333]else if [Activity Code]="A" and [Depreciation This Run]=0 and [Prior Year Expense.Depreciation This Run]<>0 then "Fully Depreciated"[/COLOR]
[COLOR=#333333]else if [Activity Code] = "A" and [Acquisition Date]>= Additions_Prior_Year_Date then "Additions"[/COLOR]
[COLOR=#333333]else if [Activity Code] = "A" and [Acquisition Date] >= Catchup_Depreciation_Date_Start and [Acquisition Date] <= Catchup_Depreciation_Date_End and [#"Placed-in-Service Date"][/COLOR]<catchup_depreciation_date_start then="" "catchup="" depreciation"="" else="" "still="" depreciating"),
 #"Added Conditional Column" = Table.AddColumn(#"Added Custom1", "New Depreciation this Run", each if [Activity Code] = "A" then [Depreciation This Run] else 0),
#"Added Custom" = Table.AddColumn(#"Added Conditional Column", "Period", each "FY 2019"),
#"Added Custom2" = Table.AddColumn(#"Added Custom", "Final Delete", each if[Coding] = "Still Depreciating" and [Depreciation This Run]=0 then "Delete" else "Do not Delete"),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom2", each [Final Delete] = "Do not Delete"),
#"Merged Queries1" = Table.NestedJoin(#"Filtered Rows1", {"Location"}, Location_Table, {"Location"}, "Location_Table", JoinKind.LeftOuter),
#"Expanded Location_Table" = Table.ExpandTableColumn(#"Merged Queries1", "Location_Table", {"Type"}, {"Location_Table.Type"})
in
#"Expanded Location_Table</catchup_depreciation_date_start>
 
Last edited:
Upvote 0
so try for Prior as first line in the code (after let before Source of course)

Key = Table.AddKey(#"Added Custom", {"Location"}, true),

for Current

Key = Table.AddKey(#"Delete some disposed items", {"Common System Number"}, true),

if it doesn't work faster try Key for: #"Merged Queries1"
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Accounts Receivable ledger via Power Query
Replies
1
Views
422
johnny51981
J
D
  • Solved
PQ slow when refreshing, load to as pivot table
Replies
1
Views
620
Matt Allington
M
C
  • Solved
Help me Convert, Merge and Transpose a pdf table into a singular row, and then repeat that step ad nauseam to create a database
Replies
5
Views
575
Clonk92
C
A
  • Question Question
Power Query performance question. Is it better to have 2 small tables merged or single large table?
Replies
3
Views
318
JEC
JEC
D
  • Question Question
Power Query refresh taking HOURS after adding custom column
Replies
3
Views
2K
Matt Allington
M

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top