Power Query slows down after merging queries or tables

bobby_smith

Board Regular
Joined
Apr 16, 2014
Messages
80
Just to make sure I understand as the file is really slow.

For prior year

Prior Year Query code


Code:
let
Source = Folder.Files("C:\Users\..........................."),
Key = Table.AddKey(#"Added Custom", {"Location"}, true),
#"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])),
For Current year



Code:
let
Source = Folder.Files("C:\Users\................"),
Key = Table.AddKey(#"Delete some disposed items", {"ommon System Number"}, true),
#"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"}),
Exactly like this followed by the remaining coding?
Also, what's the logic on selecting which field to be the Key? The Common System number is a unique value in both queries. It seems I'm using the system number in one and the location in another.

I'm just trying to get a better understanding of your thought process.

Also I'm not understanding this part "if it doesn't work faster try Key for: #"Merged Queries1"" where would I put this?

Thanks
 
Last edited:

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,730
first refresh thread and re-read first line in post#10

Code:
let
 Key = Table.AddKey(#"Added Custom", {"Location"}, true),
 Source = Folder.Files("C:\Users\..........................."),
 #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
Code:
let
 Key = Table.AddKey(#"Delete some disposed items", {"ommon System Number"}, true),
 Source = Folder.Files("C:\Users\................"),
 #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
Also I'm not understanding this part "if it doesn't work faster try Key for: #"Merged Queries1"" where would I put this?
in Prior you've two merges so if first doesn't work well try Key for the next merge:

replace with previous key:
Code:
 Key = Table.AddKey(#"Expanded Location_Table", {"Common System Number"}, true),
edit:
you need to test time (4 possibilities) how it works with single key in one query, one key for each query
btw. your code is a mish-mash to me, not optimized, sorry :)
 
Last edited:

bobby_smith

Board Regular
Joined
Apr 16, 2014
Messages
80
Thanks. I"ll try this. I did not create the code by writing/coding. All I did mostly was using the point and click feature. I'm still learning power query and I'm not advance enough to be writing full code in the M language.

I'm truly thankful for your assistance thus far.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,730
You are welcome

also you can try

Code:
in
 Table.Buffer(#"Merged Queries1")
Code:
in
 Table.Buffer(#"Expanded Location_Table")
but sometimes it makes query slower than faster so you need to test it.

btw.
on 1 000 000 rows Table.Buffer changed refresh time from 15 secs to 2.5 minutes :devilish: so be careful
 
Last edited:

bobby_smith

Board Regular
Joined
Apr 16, 2014
Messages
80
The key appears to work, but I'll not fully know until tomorrow.

Can you help me understand the primary key please. When I researched it, the syntax was
Code:
Table.AddKey(table as table,  columns as list,  isPrimary as logical) as table
The table is the name of table and column is the column with the key.

The code you gave me (ex the current year) appears to use table name as #"Delete some disposed items". Can you create any table name and use it?
Also, when you created the key for the Prior year, you used the table "#Added Custom" and then you used location as the column with the primary key.
Whats the logic on selecting which column to be the primary key? Should that column contain unique values?

Lastly, could I have use the column "Common System Number" as the primary key for both queries?

Thank you for your patience in responding to my questions as I'm truly trying to understand what is being done so I can get better at power query.

Thanks
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,730
Should that column contain unique values?
this is the best situation, merge reading each row so if there is more duplicates it will take more time
also you can use Remove Duplicates from this column, instead of the Key but from practice on my files Key+RemoveDuplicates works faster
(you need to know what are you doing :) )

could I have use the column "Common System Number" as the primary key for both queries?
as I said you've 4 possibilities, this is just 4th option :)

Prior
Code:
Key = Table.AddKey(#"Expanded Location_Table", {"Common System Number"}, true),
Current
Code:
Key = Table.AddKey(#"Delete some disposed items", {"Common System Number"}, true),
there is no any Golden Advice, you must test it yourself.

edit:
I forgot to add simple example:

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"City", type text}, {"Name", type text}, {"Date", type date}})
in
    Type[/SIZE]
Code:
[SIZE=1]// Table2
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"City", type text}, {"Name", type text}, {"Date", type date}})
in
    Type[/SIZE]
Code:
[SIZE=1]// Merge1
let
    Key = Table.AddKey(Table1, {"City"}, true),
    Source = Table.NestedJoin(Table1,{"City"},Table2,{"City"},"Table2",JoinKind.Inner),
    Expand = Table.ExpandTableColumn(Source, "Table2", {"City", "Name", "Date"}, {"Table2.City", "Table2.Name", "Table2.Date"})
in
    Expand[/SIZE]
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,730
also you can try InnerJoin instead of LeftJoin

but as I said: test it yourself
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,730
Can you help me understand the primary key please. When I researched it, the syntax was
Code:
Table.AddKey(table as table,  columns as list,  isPrimary as logical) as table
Whats the logic on selecting which column to be the primary key? Should that column contain unique values?

Definition - What does Primary Key mean?

A primary key is a special relational database table column (or combination of columns) designated to uniquely identify all table records.

A primary key’s main features are:
  • It must contain a unique value for each row of data.
  • It cannot contain null values.
A primary key is either an existing table column or a column that is specifically generated by the database according to a defined sequence.

The primary key concept is critical to an efficient relational database. Without the primary key and closely related foreign key concepts, relational databases would not work.

Almost all individuals deal with primary keys frequently but unknowingly in everyday life. For example, students are routinely assigned unique identification (ID) numbers, and all U.S. citizens have government-assigned and uniquely identifiable Social Security numbers.

For example, a database must hold all of the data stored by a commercial bank. Two of the database tables include the CUSTOMER_MASTER, which stores basic and static customer data (name, date of birth, address, Social Security number, etc.) and the ACCOUNTS_MASTER, which stores various bank account data (account creation date, account type, withdrawal limits or corresponding account information, etc.).

To uniquely identify customers, a column or combination of columns is selected to guarantee that two customers never have the same unique value. Thus, certain columns are immediately eliminated, e.g., surname and date of birth. A good primary key candidate is the column that is designated to hold Social Security numbers. However, some account holders may not have Social Security numbers, so this column’s candidacy is eliminated. The next logical option is to use a combination of columns, such as adding the surname to the date of birth to the email address, resulting in a long and cumbersome primary key.

The best option is to create a separate primary key in a new column named CUSTOMER_ID. Then, the database automatically generates a unique number each time a customer is added, guaranteeing unique identification. As this key is created, the column is designated as the primary key within the SQL script that creates the table, and all null values are automatically rejected.

The account number associated with each CUSTOMER_ID allows for the secure handling of customer queries and also demonstrates why primary keys offer the fastest method of data searching within tables. For example, a customer may be asked to provide his surname when conducting a bank query. A common surname (such as Smith) query is likely to return multiple results. When querying data, utilizing the primary key uniqueness feature guarantees one result.


 

Watch MrExcel Video

Forum statistics

Threads
1,101,817
Messages
5,483,080
Members
407,377
Latest member
JennaWashburn

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top