Check Values from another Table

[Anonymous]

Hi,

New to Power Query and have been searching for days for what I think should be not too hard to do, but have not been able to find anyting that I can get working...

Thanking you in advance...

The first table called "Initials", I need to check the below 4 columns and against the second table called "eTender Report All"

“Initial” Table column to check-

Contract Number (This is Text format)

Vendor Name (This is Text format)

Contract Value (Incl. GST) (This is Currency format)

Contract Aware Date (This is date format)

“eTender Report All” Table columns to check-

Contract Award Notice ID (This is Text Format)

Successful Contractor (This is Text Format)

Estimated amount payable to the contractor (including GST) $ (This is Currency format)

Effective Date (This is Date format)

These columns are matched for the same type of data

I need to return the values that match into a new column called “Duplicate Check” in the “Initials” table with the first match being between the” Initials” table “Contract Number” column, and the “eTender Report All” table, “Contract Award Notice ID” column. If this does not match, then update the new column called “Duplicate Check” with “No Contract ID Match” and end the check.

If the Contract Number and the Contract ID Award Notice do match, then I need to check the other 3 columns for matches and add updates to the new column call Duplicate Check for all the fields that match in the following or similar format-

“Duplicate Check” Column Output

Contract Award Notice ID Match |Successful Contractor Match | Estimated amount payable to the contractor (including GST) $ Match | Effective Date Match

Link to data file- Sample Data 

Regards,

Peter

[v-jingzhang]

Hi @Anonymous 

I think the Merge queries feature would be helpful. In "Initials" table, click on "Merge Queries" to open the Merge window. Select "Contract Number" (in "Initials" table) and “Contract Award Notice ID” (in “eTender Report All” table) as the matching column. Use Left Outer join kind. 

You will get a matching table column in "Initials" table. Expand the table column. Then add a custom column with below code. Finally remove unnecessary columns. 

if [Contract Award Notice ID] = null then "No Contract ID Match" else "Contract Award Notice ID Match" & (if [Vendor Name] = [Successful Contractor] then " | Successful Contractor Match" else "") & (if [#"Contract Value (Incl.GST)"] = [#"Estimated amount payable to the contractor (including GST) $"] then " | Contract Value (Incl.GST) Match" else "") & (if [Contract Award Date] = [Effective Date] then " | Effective Date Match" else "")

The sample file has been attached at bottom. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

Hi Jing,

Thank you so much for your help on this issue I have.

I have reviewed your file and  followed the instructions, but I am getting one issue that is causing an error in the Duplicate Check column that I am not understanding...

Expression.Error: The field 'Successful Contractor' of the record wasn't found.

That error, as I understand it is telling me that it was unable to find the column 'Successful Contractor'...

But when I look at the error text I can see that is retrieving the correct information-

Vendor Name=Madeupcompany Pty Limited

Successful Contractor=Made Up Company Name  Pty Ltd

I do note that the Vendor Name and Company Name have dirrerent formatting, so I changed this field to be the Company Number and the result was the same error for the different field...

Thank you again...

Regards,

Peter

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Please check the step "Expanded Table2Name". Do you have any string as default column name prefex (optional) when expanding? If the prefex is blank, the columns from Table2 will be expanded with their original names. 

If the prefex is not blank, the columns after expanding will have column names like Table2Name.ColumnName. When you expand the table column at first time, it will use the table name as prefex by default. You need to uncheck the option "Use original column name as prefix" if you don't want that prefex.

When creating a custom column, to avoid entering an incorrect column name, you can select a column in Available columns box and click "< < Insert" button to enter this column to the formula, or double-click on a column in Available columns box. 

Hope this would be helpful. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

Thank you so much that worked!

Regards,

Petre

[Anonymous]

Hi All- is anyone able to give me a hint with this one please?

Sorry for being a pain but I have some time restraints on this...

cheers,

peter