Forum Discussion

ND_Pard's avatar
ND_Pard
Helper II
3 years ago
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 ...
Need Help
  • smpa01's avatar
    smpa01
    3 years ago
    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"
smpa01's avatar
smpa01
Community Champion
3 years ago
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's avatar
ND_Pard
Helper II
3 years ago

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