Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
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 |
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
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
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
111 | |
96 | |
96 | |
67 | |
63 |