# Power Query slows down after merging queries or tables



## bobby_smith (Jul 3, 2019)

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.


----------



## sandy666 (Jul 4, 2019)

post M-code , use [CODE]_your M-code here_[/CODE]


----------



## peter789 (Jul 4, 2019)

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


----------



## bobby_smith (Jul 4, 2019)

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>


----------



## sandy666 (Jul 4, 2019)

could you edit your post and use CODE tags?

you've 10 minutes from time of your post: 06:43 PM - 06:53 PM


----------



## bobby_smith (Jul 4, 2019)

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.


----------



## sandy666 (Jul 4, 2019)

which query is slow? first or second?

you said you add a KEY but I don't see that


----------



## bobby_smith (Jul 4, 2019)

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.


----------



## bobby_smith (Jul 4, 2019)

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

```
[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

```
[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>
```


----------



## sandy666 (Jul 4, 2019)

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"


----------



## bobby_smith (Jul 3, 2019)

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.


----------



## bobby_smith (Jul 4, 2019)

Just to make sure I understand as the file is really slow.

For prior year

Prior Year Query 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




```
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


----------



## sandy666 (Jul 4, 2019)

first refresh thread and re-read first line in post#10


```
let
 Key = Table.AddKey(#"Added Custom", {"Location"}, true),
 Source = Folder.Files("C:\Users\..........................."),
 #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
```


```
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:

```
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


----------



## bobby_smith (Jul 4, 2019)

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 (Jul 4, 2019)

You are welcome

also you can try 


```
in
 Table.Buffer(#"Merged Queries1")
```


```
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  so be careful


----------



## bobby_smith (Jul 4, 2019)

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 

```
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 (Jul 4, 2019)

bobby_smith said:


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



bobby_smith said:


> 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

```
Key = Table.AddKey(#"Expanded Location_Table", {"Common System Number"}, true),
```
Current

```
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:


```
[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]
```


```
[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]
```


```
[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]
```


----------



## sandy666 (Jul 4, 2019)

also you can try InnerJoin instead of LeftJoin

but as I said: test it yourself


----------



## sandy666 (Jul 4, 2019)

bobby_smith said:


> Can you help me understand the primary key please. When I researched it, the syntax was
> 
> ```
> Table.AddKey(table as table,  columns as list,  isPrimary as logical) as table
> ...





*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.

Technopedia​


----------

