Forum Discussion
Lookupvalue in Power Query
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.
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"
2 Replies
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"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.
