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

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"

6 Replies

  • KJanssens's avatar
    KJanssens
    Helper II
    3 years ago

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

  • ND_Pard's avatar
    ND_Pard
    Helper II
    3 years ago

    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's avatar
      KJanssens
      Helper II
      3 years ago

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

  • 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

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