Comparing two tables with only first value of column

[Ekaterina_]

Hello everyone, 

I would appreciate if someone can me help in the folliwng problem:

I have two tables, in the first table the column "Projetct" can have several entries but I want to compare only the first value in this column with the value in my second table and return the ActivityID of the second table in a new column "ProjectID" in the first table.

Here are my tables:

TblSplit

No.	Project
1	Pj: Mig,Pj: CustomAppl
2	Pj: CustomAppl
3	Pj: Exec
4	Pj: Exec,Pj: Mig,Pj: AllianceComm

TblPjID

(-> this table is filled out manually and source table for MD_003_TblTDIPj containing the same two columns)

Project	ActivityID
Pj: Exec	SWP-Exec
Pj: Mig	LLP-Mig
Pj: CustomAppl	SWP-CustomAppl
Pj: AllianceComm	AS-AllianceComm

Result I want to see in TblSplit:

No	Project	ActivityID
1	Pj: Mig,Pj: CustomAppl	LLP-MIG
2	Pj: CustomAppl	SWP-CustomAppl
3	Pj: Exec	SWP-Exec
4	Pj: Exec,Pj: Mig,Pj: AllianceComm	SWP-Exec

What I have tried out:

AppendTbl_TDIPj = Table.NestedJoin(Source, {"Project"}, MD_003_TblTDIPj, ("Project"), " TblPjID, JoinKind.LeftOuter),

ExpTbl_PjActivityID = Table.ExpandTableColumn(AppendTbl_TDIPj, {"ActivityID_Pj"}, {"TblPjID.ActivityID_Pj"})

In ExpTbl_PjActivityID

As a result a get null for row no 1 and 4

Thanks in advance for any help!

Kind regards,

Ekaterina 

[Ahmedx · Accepted solution]

pls try

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrIslLwzUzXAdHOpcUl+bmOBQU5SrE60UpGUGk0YWOosGtFajJYwARJQAfZPMecnMzEvORU5/zcXKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [No = _t, Project = _t]),
    lst = TblPjID[ActivityID],
   f=(x)=>[a=Table.PositionOf(TblPjID,x,Occurrence.Last,g),b= {"NOMATCH",lst{a}}{ Byte.From(a>-1)}][b],
   g=(x,y)=>   Text.Split( y[Project],","){0}=x[Project],
     to = Table.AddColumn(Source,"ActivityID",f)
     in
     to

[wdx223_Daniel]

NewStep=Table.AddColumn(Source,"ActivityID",each MD_003_TblTDIPj{[Project=Text.Split([Project],","){0}]}?[ActivityID]?)

[Omid_Motamedise]

go to TblSplit and add a new column into it based in the next formula

Table.SelectRows(TblPjID,(X)=>X[Project]=Text.Split([Project],","){0})[ActivityID]{0}

it results in (for the second row leads to error because of extra space in the notation)

[dufoq3 · Accepted solution]

Hi @Ekaterina_, check this:

Result

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrIslLwzUzXAdHOpcUl+bmOBQU5SrE60UpGUGk0YWOosGtFajJYwARJQAfZPMecnMzEvORU5/zcXKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"No." = _t, Project = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsiyUnCtSE1W0lEKDg/QBTNjdSDivpnpQGEfnwBdEAsm6lxaXJKf61hQkKMA1YQQgStyzMnJTMxLTnXOz80FqnIM1kURiY0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, ActivityID = _t]),
    T1_Ad_Helper = Table.AddColumn(Table1, "Helper", each Text.Trim(Text.BetweenDelimiters([Project], ":", ",", 0, 0)), type text),
    T2_ExtractedProject = Table.TransformColumns(Table2, {{"Project", each Text.Trim(Text.AfterDelimiter(_, ":")), type text}}),
    MergedQueries = Table.NestedJoin(T1_Ad_Helper, {"Helper"}, T2_ExtractedProject, {"Project"}, "T2", JoinKind.LeftOuter),
    ExpandedT2 = Table.ExpandTableColumn(MergedQueries, "T2", {"ActivityID"}, {"ActivityID"}),
    RemovedColumns = Table.RemoveColumns(ExpandedT2,{"Helper"})
in
    RemovedColumns

[Ahmedx · Accepted solution]

pls try

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrIslLwzUzXAdHOpcUl+bmOBQU5SrE60UpGUGk0YWOosGtFajJYwARJQAfZPMecnMzEvORU5/zcXKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [No = _t, Project = _t]),
    lst = TblPjID[ActivityID],
   f=(x)=>[a=Table.PositionOf(TblPjID,x,Occurrence.Last,g),b= {"NOMATCH",lst{a}}{ Byte.From(a>-1)}][b],
   g=(x,y)=>   Text.Split( y[Project],","){0}=x[Project],
     to = Table.AddColumn(Source,"ActivityID",f)
     in
     to