Forum Discussion
Solved
Create a Custom Field that returns a Lookup Value
I have a table with a field for Product_ID and Product Description. The Product_ID field is a six character text field which is poputlated with numeric (non-alphabetic) data. If the Product_ID end ...
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0MDBQ0lFKVIrVgfAMgbwkOM8IyEuG84yBvBSl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, val = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.End([Column1], 3)<>"000" then Text.Start([Column1],2)&"000"else [Column1]), #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Custom"}, #"Added Custom", {"Column1"}, "Added Custom", JoinKind.LeftOuter), #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"val"}, {"Added Custom.val"}) in #"Expanded Added Custom"
I am hoping for a solution that does not involve creating another table.
If that is the only solution, I can just load the data to Excel and use the xLookUp function to add the field.
=xLookup(A2,A:A:,B:B,B2)
as Excel can easily handle the loaded data.
Thanks though ...
