M Code to extract IO number

[nzaccountant]

hi there

I have a list of Machinery Items with Internal order (IO) Numbers. 

I then have a list of transactions with the IO number contained in it. 

I would like to write some sort of M code which attempts to do the following:

1) Extracts the IO numbers from the machinery list

2) If one of these IO numbers is found in the transaction list, add the IO as a custom column

3) If no IO is found, return BLANK. 

Sample Machinary list:

IO Number	Machinary Name
T453	Tractor 1
Y654	Sprayer 1
F435	Tractor 2

Sample transaction list

Transaction Detail	IO Number?
Service for T453
F435 Service
Work of Y654

The goal is to create a list of machinary items that has a relationship with the transaction table via a new column called IO number. 

Thanks

[v-yangliu-msft · Accepted solution]

Hi  @nzaccountant ,

Here are the steps you can follow：

1. Select add column - index column - from 1 in the power query to add indexes for both tables.

2. Select Home-Merge Queries-Merge Queries, merge the two tables using left Outer according to [Index],

3. Select Add column – Custom column

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.