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"
you would have to work with an intermediate table, that uses your main table, but only keeps the "000" records.
then in the main table you generate a new column that takes the first three characters of the productID and adds "000" to it. This column you can use to add the additional table...