Help matching cells in a table based on the WBS ID

[denn9]

I am attempting to bridge two tables that use different reference codes for projects. One uses a numbered ID and the other uses a WBS ID. I need to create a one to one relationship, but one of the tables is broken down at a smaller level. What I mean by this is that the IDs from one table need to match an ID from the other table like this:

The problem is that the table with the WBS codes has them broken down even further, so that for every numbered ID, there are several WBS IDs. Like this,

There are thousands of rows, so it is not feasible to match each manually. Is there a way to make it so that every WBS ID that starts with "WBS.10.09" is matched to the Project ID 41?

Thank you for your help!

[techies · Accepted solution]

Hi @denn9 pls try adding calculated columns in the detailed WBS table

WBS_Prefix =

VAR Parts = SUBSTITUTE([WBS_Code], ".", "|")

VAR FirstSep = FIND("|", Parts)

VAR SecondSep = FIND("|", Parts, FirstSep + 1)

VAR ThirdSep = FIND("|", Parts, SecondSep + 1)

VAR Prefix = LEFT(Parts, ThirdSep - 1)

RETURN SUBSTITUTE(Prefix, "|", ".")

ProjectID =

LOOKUPVALUE(

    ProjectMap[ProjectID],

    ProjectMap[WBS_Prefix],

    DetailedWBS[WBS_Prefix]

)

[Anonymous]

Hi @denn9,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[Anonymous]

Hi @denn9,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[Anonymous]

Hi @denn9,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,
Vinay Pabbu

[collinq]

Hi @denn9 ,

@techies Suggestion will probably work and may be the more elegant solution.  I was thinking the easiest way would be to make a column based on the WBS Id where you strip off the last 3 charaacters.  And then, match that to the ID's

[techies · Accepted solution]

Hi @denn9 or add a custom column in the power query editor in the detailed WBS table

Text.BeforeDelimiter([WBS_Code], ".", 2)

then merge queries

[techies · Accepted solution]

Hi @denn9 pls try adding calculated columns in the detailed WBS table

WBS_Prefix =

VAR Parts = SUBSTITUTE([WBS_Code], ".", "|")

VAR FirstSep = FIND("|", Parts)

VAR SecondSep = FIND("|", Parts, FirstSep + 1)

VAR ThirdSep = FIND("|", Parts, SecondSep + 1)

VAR Prefix = LEFT(Parts, ThirdSep - 1)

RETURN SUBSTITUTE(Prefix, "|", ".")

ProjectID =

LOOKUPVALUE(

    ProjectMap[ProjectID],

    ProjectMap[WBS_Prefix],

    DetailedWBS[WBS_Prefix]

)