Forum Discussion

strdst2090's avatar
strdst2090
Frequent Visitor
2 years 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 Des...
Need Help
  • Ahmedx's avatar
    Ahmedx
    2 years 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"
Claude_Xu's avatar
Claude_Xu
Frequent Visitor
2 years 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