Forum Discussion
Ekaterina_
Helper I
2 years agoComparing 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 ...
- 2 years ago
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 - 2 years ago
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
dufoq3
Community Champion
2 years agoHi 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