Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Ekaterina_
Helper I
Helper I

Comparing two tables with only first value of column

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
1Pj: Mig,Pj: CustomAppl
2Pj: CustomAppl
3Pj: Exec
4Pj: Exec,Pj: Mig,Pj: AllianceComm

 

TblPjID

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

ProjectActivityID
Pj: ExecSWP-Exec
Pj: MigLLP-Mig
Pj: CustomAppl SWP-CustomAppl
Pj: AllianceCommAS-AllianceComm

 

Result I want to see in TblSplit:

NoProject ActivityID  
1Pj: Mig,Pj: CustomApplLLP-MIG 
2Pj: CustomApplSWP-CustomAppl
3Pj: ExecSWP-Exec
4Pj: Exec,Pj: Mig,Pj: AllianceCommSWP-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 

2 ACCEPTED SOLUTIONS
Ahmedx
Super User
Super User

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

Screenshot_1.png

View solution in original post

dufoq3
Super User
Super User

Hi @Ekaterina_, check this:

 

Result

dufoq3_0-1725455697554.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

4 REPLIES 4
wdx223_Daniel
Super User
Super User

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

Omid_Motamedise
Super User
Super User

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)

Omid_Motamedise_0-1725489420867.png

 

If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h
dufoq3
Super User
Super User

Hi @Ekaterina_, check this:

 

Result

dufoq3_0-1725455697554.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Ahmedx
Super User
Super User

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

Screenshot_1.png

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors