Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
strdst2090
Frequent Visitor

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.

 

strdst2090_3-1725613896625.png

 

 

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.

 

strdst2090_2-1725613859725.png

 

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.

 

strdst2090_1-1725613826682.png

 

Can someone help me fix this?

1 ACCEPTED SOLUTION
Ahmedx
Super User
Super User

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"

View solution in original post

8 REPLIES 8
Ahmedx
Super User
Super User

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
Community Support
Community Support

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 

vxinruzhumsft_0-1725851235324.png

 

vxinruzhumsft_1-1725851243289.png

 

Ouptut

vxinruzhumsft_2-1725851254051.png

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.

 

 

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

Claude_Xu
Frequent Visitor

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

 

Claude_Xu_0-1725793338510.png

 

dufoq3
Super User
Super User

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]

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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?

@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?

 

 

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"


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.