Index match in power query form 2 different files

[Anonymous]

Hi all

I would like to replicate the index match function in power query, however i have a table of data which has the look up value, and the table where i want to look up form is in a different excel file. 

example 

Main table (look up table and results file

 Project ID = SAS_001

Finance table 

   Project ID = SAS_001

   SAP_ID1  = ""

   SAP_ID2  = BI.130016 

I am wanting the function to return the SAP_ID1 or SAP_ID2 depending which one has a value,  based on the lookup value of the Project ID from the main table. 

expected result is BI.130016

thank you

[v-frfei-msft]

Hi @Anonymous,

Could you please share your sample data and excepted result to me, if you don't have confidential data? Please upload your file to One Drive and share the link here.

Regards,

Frank

[Anonymous]

hi

Base on the Project number I want to pull the date from the last 2 columes of data into to one colume in the main table result in bold

Finance file

Project Number	Project Name	Business SAP Project Number	Tech SAP Project Number
CAT_0003	Data Migration Specialist Resources _ (CAT)	BI.170067
CAT_0004	Migration Management Tool and Services  _ (CAT)	BI.170068
CDO_500	Data Management Execution		IN.170181
CDO_501	Data Foundation - Data Management		IN.160212

Main table

ID	SAP ID	Project name
CAT_0003	BI.170067	Data Migration Specialist Resources _ (CAT)
CAT_0004	BI.170068	Migration Management Tool and Services  _ (CAT)
CDO_500	IN.170181	Data Management Execution
CDO_501	IN.160212	Data Foundation - Data Management

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

To create a calculated column as below.

Column =
CONCATENATE (
    LOOKUPVALUE (
        Table1[Tech SAP Project Number],
        Table1[Project Number], Table2[ID]
    ),
    LOOKUPVALUE (
        Table1[Business SAP Project Number],
        Table1[Project Number], Table2[ID]
    )
)

Regards,

Frank

[v-frfei-msft]

Hi @Anonymous ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank