Forum Discussion

Ekaterina_'s avatar
Ekaterina_
Icon for Helper I rankHelper I
2 years ago
Solved

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 ...
  • Ahmedx's avatar
    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

  • dufoq3's avatar
    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