Extract data using lookup table

[strdst2090]

I need help refining a function/building a solution from scratch to a PowerBI usecase I have.

I have a table that represents journal data. The principle fields are Journal ID, Project Name, Line Description, Long Header Description, Vendor Name, Spend in USD. The table is an excel report generated from Peoplesoft. The field Vendor name is an optional field in PS and therefore has a lot of null values. The problem statement here is to populate the column 'Vendor name' with appropriate and accurate vendor names.

If I were to resolve this manually, this is what I'd do. In the event that the Vendor Name field is empty, I would look up the fields Project Name, Line Description, Long Header Description to see if there are any vendor names in either of the fields (usually people mention the vendor name along with what they've spent it for, in the line description field), and if there is, I'll enter that vendor name in the 'Vendor Name' field. If I don't find anything, I just leave it empty.

Now, I have a lookup table that has 2 fields Vendor Name & Normalized Vendor Name. Remember how I mentioned people tend to mention the vendor name in the line description/long header description field? So, people tend to call the same vendor name, different ways. For ex, Microsoft can be mentioned simply as Microsoft, or Microsoft Corp,. Microsoft Corporation, Microsoft Ltd. etc. The field Vendor name has these different known possibilities and the normalized vendor name field has the normalised name - ie. for the Microsoft example mentioned above, the normalized name field would have Microsoft against all known user-entered values of Microsoft.

In order to populate the vendor name field, I wrote a very primitive function that looks for any matches between the line description field and the vendor name field in the lookup table. If there's a match, then it outputs the value in the corresponding 'Normalized Vendor Name' field. Unfortunately, the function I wrote ... is looking for partial matches as opposed to full matches. Basically it's broken.

Can someone help me fix this?

[Ahmedx · Accepted solution]

pls try this

let
    S  =  List.Buffer( #"Vendor Table"[Vendor Name]&{""}),
    R  =  List.Buffer( #"Vendor Table"[Normalized Name]&{null}),
    f=(x)=>((a)=>R{List.PositionOf(S,a,0,(c,v)=>Text.Contains(v,Text.Lower(c)))})(Text.Lower(x)),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRydlGK1YlWcnVz9wAzQlKLSwwNTUzNzC3gfEsvzzCXYK8wf6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Line Description" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Line Description", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Result", each f([Line Description]))

in
  #"Added Custom"

[v-xinruzhu-msft]

Hi,

Thanks for the solutions dufoq3 , PwerQueryKees and Claude_Xu offered, and i want to offer some more information for user to refer to.

hello @strdst2090 , you can try the following custom column.

=List.Accumulate(List.Numbers(0,Table.RowCount(#"Vendor Table"),1),null,(x,y)=>if Text.Contains([Line Description],#"Vendor Table"[Vendor Name]{y}) then #"Vendor Table"[Normalized Name]{y} else x)

Sample data 

Ouptut

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[strdst2090]

I tried this, but it sent me into an infinity loop.

[Claude_Xu]

Hi, I think dufoq3 is actually very close to the answer. Below is my attempt. 

let
    TestJournalTable = #table({"JournalID", "LineDescription"}, {
        {"AP06022443", "Invoice for MS"},
        {"AP06022444", "Invoice for MICROSOFT"},
        {"AP06022445", "Invoice for microsoft"},
        {"AP06022446", "Invoice for Microsoft"},
        {"AP06022447", "Invoice for Microsoft Ltd."},
        {"AP06022448", "Microsoft Corporation"},
        {"AP06022449", "Accruals for IBM"},
        {"AP06022450", "IBM Inc."},
        {"AP06022451", "Receipt for ibm"}
    }),
    TestLookupTable = #table({"VendorName", "NormalizedVendorName"}, {
        {"MS", "Microsoft"},
        {"Microsoft","Microsoft"},
        {"IBM", "IBM"}
    }),
    LookupNormalizedVendorNameFn = (DescWithVendorName as text, LookupTable as table) as text => let
        MatchedLookupTable = Table.SelectRows(LookupTable, each Text.Contains(Text.Upper(DescWithVendorName), Text.Upper([VendorName]))),
        ReturnedVendorName = if Table.RowCount(MatchedLookupTable) = 0 then "" // Leave empty or probably return a flag like N/A
            else if Table.RowCount(MatchedLookupTable) = 1 then MatchedLookupTable[NormalizedVendorName]{0}
            else  "<Multiple Matched>" // ideally sholdn't happen, but what if we get lookup table configured incorrectly
    in ReturnedVendorName,
    TestJournalTableWithNormalizedVendor = Table.AddColumn(
        TestJournalTable, 
        "NormalizedVendorName", 
        each LookupNormalizedVendorNameFn([LineDescription], TestLookupTable)
    ) 
in
    TestJournalTableWithNormalizedVendor

[dufoq3]

Hi @strdst2090, replace your Custom Column code with this one and let me know. This should check for exact match.

[ a = Table.Buffer(#"Vendor Table"),
  b = Table.SelectRows(a, (x)=> x[Vendor Name] = [Line Description])[Normalized Name]{0}?
][b]

[strdst2090]

Hi @dufoq3 . The code you sent, works in cases where the Line Description field contains an exact value as in the Vendor Table (ie. the lookup). I have many records where the line description field looks like this - 'Invoice for Microsoft', 'Accruals for IBM'. In cases like these, theoretically, a partial match would work. In excel, we would use Vlookup(*value*, lookuptable, return column, (True/False)). How do I accomplish the same in PowerBI using M?

[PwerQueryKees]

@strdst2090 

Replace

x[Vendor Name] = [Line Description]

with

Text.Contains(x[Vendor Name], [Line Description],Comparer.OrdinalIgnoreCase)

@dufoq3 I have 2 questions (trying to learn here):

1. I notice you use the M Language Record syntax instead of a let/in statement. Why?
2. What does the question mark after the {0} do?

[dufoq3]

Hi, 1.) it is the same as len in block in general, but little advantage is that you can see output of each step when you just end as record ]

2.) If you use questionmark it will return null if there is no value. Without question mark it will retun an error. You can use also coalesce operator ?? which let you return anything you want in case the value is null i.e. {0}? ?? "some text instead of null"