Lookupvalue in Power Query

[Saxon202202]

Hi,
I have table below likt that, some of the codes are missing the description against the item. I want to create a custom column in Power Query and re-generate the description according to the item within the same table. ( I don't want to duplicate the table due to large amount of data). Sometimes the code doesn't have description in this case return blank.

https://www.dropbox.com/scl/fi/m82rzndp35n92p7alrwtg/Power-Query.xlsx?dl=0&rlkey=ecsrehgbtrpyk55dgw3...

[MFelix · Accepted solution]

Hi @Saxon202202 ,

For this you need to create an intermidiate step that make a group with the maximum or minimum, but has @andhiii079845 , be carefull with having more than one value for each code, in this case I'm picking up only the minimum.

Then you do a merge of the table with itself and you get the final result.

• Group rows by code and minimum of description (I have kept your result for comparisions purposes):

• Merge the table with itself:

• Update the step on the query refering to the last step before the grouping in my case is the Changed Type:

= Table.NestedJoin(#"Changed Type", {"Code"}, #"Grouped Rows", {"Code"}, "Grouped Rows", JoinKind.LeftOuter)

• Expand the column Description Final

Complete code below and in attach file.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}, {"Description", type text}, {"Result(Description)", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Code"}, {{"Description Final", each List.Min([Description]), type nullable text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Code"}, #"Grouped Rows", {"Code"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Description Final"}, {"Description Final"})
in
    #"Expanded Grouped Rows"

[andhiii079845]

The important point is if the code have everytime the same text if not blank. Not like: 123 apple , 123 appl , 133 appple. i would create a own dimensional table for the lookup values and use than the merge function.