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?

strdst2090

Frequent Visitor

1 year ago

Solved

Extract data using lookup table

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?

Need Help

Ahmedx

1 year ago

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"

Extract table.pbix28 KB

8 Replies

dufoq3
Community Champion
1 year ago
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
  Frequent Visitor
  1 year ago
  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
    Super User
    1 year ago
    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):
    I notice you use the M Language Record syntax instead of a let/in statement. Why?
    What does the question mark after the {0} do?

Claude_Xu

Frequent Visitor

1 year ago

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

Anonymous
Not applicable
1 year ago
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.

Extract data using lookup table.pbix27 KB
- strdst2090
  Frequent Visitor
  1 year ago
  I tried this, but it sent me into an infinity loop.

Ahmedx

Super User

1 year ago

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"

Extract table.pbix28 KB

Forum Discussion

Extract data using lookup table

8 Replies

Related content

Dynamic filter using lookups

Fabric metric extraction using rest api

Extract Data from sharepoint

Lookup help

Extracting Teams call info using Graph API