Create a Custom Field that returns a Lookup Value

[ND_Pard]

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 with three (3) zeroes, it is actually a roll-up for all other Product_IDs that begin with the same first three (3) numeric characters.  For Example:
111000      Paper
111001      Paper 8"x11"
111002      Paper 8"x14"
111003      Paper 14"x14"

111000 is never used for actual inventory, it's simply a roll-up for costs, counts, etc. of the products whose Product_ID that start with 111.

I would like to use Power Query to create a custom field that would populate with the product description of the Product_ID that starts with their first three characters and that ends with three zeros.  For Example:
111000      Paper                  Paper
111001      Paper 8"x11"      Paper
111002      Paper 8"x14"      Paper
111003      Paper 14"x14"    Paper

Thanks in advance.

[smpa01 · Accepted solution]

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"

[ND_Pard]

smpa01,

Your solution worked!  
My Advanced Editor contains the following:
let
Source = Csv.Document(File.Contents("Q:\SO-Finance\PSft_Qry_csv_Files\NDS325_ACCOUNTS_REV_N_EXP.csv"),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", each if Text.End([Acct],2) <> "000" then Text.Start([Acct],3) & "000" else [Acct]),
#"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Custom"}, #"Added Custom", {"Acct"}, "Added Custom",JoinKind.LeftOuter),
#"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Acct_Desc"}, {"Added Custom.Acct_Desc"})
in
#"Expanded Added Custom"

Included is picture of the results.  Thank you very much!!!!

[ND_Pard]

This 'might' work ... I only work Mon & Tue ... as I am retired.  I'll take a look at it next week and, if it works, I'll acknowledge your input accordingly.

Thanks smpa01!

[ND_Pard]

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 ...

[KJanssens]

You would not need to create the table yourself, but like @smpa01 has demonstrated, the intermediate table is derived from the source data and merged back into the main table...

Excel would probably work as well, but might become problematic if you have a lot of data...

[KJanssens]

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...