Forum Discussion
Extract data using lookup table
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"
Community ChampionHi 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]
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
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?
- 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"
