Forum Discussion

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

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"

Extract table.pbix28 KB

Anonymous

Not applicable

2 years 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