Forum Discussion
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 |
| 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
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 toHi 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
4 Replies
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)
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 toHi 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 RemovedColumnsNewStep=Table.AddColumn(Source,"ActivityID",each MD_003_TblTDIPj{[Project=Text.Split([Project],","){0}]}?[ActivityID]?)
