Search from two columns in other table and retrieve corresponding value from other column?-CodePudding

I have the input tables TblA and TblB. I want to search values of columns NUM and STR from TblA within table TblB.

Below is my current code, that looks for values of column NUM of TblA within column VALUES of TblB and gets the corresponding values of column CODE_T.

How to modify the step Table.AddColumn() in order to search the numeric parts (joined from right to left) of values in column STR of TblA within column CODE_M of TblB.

The numeric parts in inverse order I could get them in this way

    str1 = Text.Middle([CODE_M],14,3),
    str2 = Text.Middle([CODE_M],7,3),
    final_string_to_search = str1 & str2,

This is my expected output

This is my current code. Thanks in advance.

let
    TblA = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI2MQaSSrE60UqRQIaRoRGMGwVkGBqamsP4ESCGjlJiUrJecWqyobm5XlpmkYmJkV55QWFKYkkiWJEbiiIDC0OQIktDY4SiWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TRS = _t, NUM = _t, STR = _t]),
    TblB = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JYsxDsMwCEXvwpzB32ADY6oM7dAmipQqkuX7X6PY3d77PFojR6KFkMRZAp7Xl6kvjYCi4WpezQM 6zvNg1gJLRWFEbA/bsw9DxPJ0PF3nLvOmYVDs6PWHHCu2z 3mcFEeQSv7Xbq/Qc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [VALUES = _t, CODE_M = _t, CODE_T = _t]),

    Output
    = Table.AddColumn(
        TblA,
        "OUT",
        each
        let
            val = [NUM]
        in
            Text.Combine(Table.SelectRows(TblB, each Text.Contains(val, [VALUES]))[CODE_T], ",")
    )
in
    Output

CodePudding user response：

Here's one way of doing it:

I chose to use Join rather than SelectRows because I think it executes more efficiently, (but I haven't actually tested it).

I assumed there will be only a single match in TblB for any row in TblA. If that is not guaranteed, you need to specify what you want for output if multiple matches should occur. Current code will return only the first match.

Add a custom column to TblA for the reversed numbers in STR
Left.Outer join between TblA and TblB with NUM and VALUES as the keys
Then Left.Outer join between the first join and TblB using STRkey and CODE_M
Remove the unneeded columns:

let
    TblAOrig = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI2MQaSSrE60UqRQIaRoRGMGwVkGBqamsP4ESCGjlJiUrJecWqyobm5XlpmkYmJkV55QWFKYkkiWJEbiiIDC0OQIktDY4SiWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TRS = _t, NUM = _t, STR = _t]),

//add column with the reversed numbers in STR
//assumes the numbers are in 2nd and 3rd dot separated strings and at the end of those substrings
//  but the number of digits in the string are unspecified
    TblA  = Table.AddColumn(TblAOrig, "STRkey", 
        each if [STR]="" then null else
            let 
                Split1=List.Range(Text.Split([STR],"."),1,2),
                nums=List.Transform(Split1, 
                        (li)=>Splitter.SplitTextByCharacterTransition((c)=>not List.Contains({"0".."9"},c),{"0".."9"})(li){1}),
                key=nums{1} & nums{0}
            in 
                key),

    TblB = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JYsxDsMwCEXvwpzB32ADY6oM7dAmipQqkuX7X6PY3d77PFojR6KFkMRZAp7Xl6kvjYCi4WpezQM 6zvNg1gJLRWFEbA/bsw9DxPJ0PF3nLvOmYVDs6PWHHCu2z 3mcFEeQSv7Xbq/Qc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [VALUES = _t, CODE_M = _t, CODE_T = _t]),
    
    joinVALUES = Table.Join(TblA,"NUM",TblB,"VALUES",JoinKind.LeftOuter),
    joinCODE_M = Table.NestedJoin(joinVALUES,"STRkey",TblB,"CODE_M","Join", JoinKind.LeftOuter),
    
    #"Added Custom" = Table.AddColumn(joinCODE_M, "OUT", each if [CODE_T] <> null 
            then [CODE_T]
        else if List.Count([Join][CODE_T]) = 0
            then "NOT FOUND"
        else [Join][CODE_T]{0}, type text),
    
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"STRkey", "VALUES", "CODE_M", "CODE_T", "Join"})
in
    #"Removed Columns"

TblA

TblB

Result

If the output must be in a certain order, one can add an Index column to the TblA, and use that to sort the rows back to the original order.

CodePudding user response：

Alternate method

let
TblA = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI2MQaSSrE60UqRQIaRoRGMGwVkGBqamsP4ESCGjlJiUrJecWqyobm5XlpmkYmJkV55QWFKYkkiWJEbiiIDC0OQIktDY4SiWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TRS = _t, NUM = _t, STR = _t]),
TblB = Table.Buffer( Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JYsxDsMwCEXvwpzB32ADY6oM7dAmipQqkuX7X6PY3d77PFojR6KFkMRZAp7Xl6kvjYCi4WpezQM 6zvNg1gJLRWFEbA/bsw9DxPJ0PF3nLvOmYVDs6PWHHCu2z 3mcFEeQSv7Xbq/Qc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [VALUES = _t, CODE_M = _t, CODE_T = _t])),
#"Added Custom" = Table.AddColumn(TblA,"OUT",(i)=>try Table.SelectRows(TblB, each i[NUM]=[VALUES] or Text.Middle(i[STR],14,3)=Text.Start([CODE_M],3))[CODE_T]{0} otherwise "NOT FOUND")        
in #"Added Custom"