Formula from input to list all outputs which data comes from a table

[samzie83]

Input	Apple	Ref No	Item 1	Item 2
		1	Apple	Banana
Output	Banana	2	Berries	Coconut
	Watermelon	3	Watermelon	Apple
	Berries	4	Banana	Coconut
	Lettuce	5	Berries	Apple
		6	Lettuce	Cucumber
		7	Apple	Lettuce

<tbody>
</tbody>

 

[samzie83]

Hi, I am trying to figure out from the list on the right, how I can set an Input "Fruit" and then the Output will populate in order down the table listing all Outputs that have the Input on the same line.

 

[sandy666]

you can try PowerQuery (aka Get&Transform)

load source table into PQ
create input table and load into PQ

Ref No	Item 1	Item 2		Input		Output
1​	Apple	Banana		Banana		Apple
2​	Berries	Coconut				Coconut
3​	Watermelon	Apple
4​	Banana	Coconut
5​	Berries	Apple
6​	Lettuce	Cucumber
7​	Apple	Lettuce

then use :

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table1),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Ref No", "Item 1", "Item 2"}, {"Ref No", "Item 1", "Item 2"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Custom", "Output", each if [Input] = [Item 1] then [Item 2] else if [Input] = [Item 2] then [Item 1] else null),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column",{"Output"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Output] <> null))
in
    #"Filtered Rows"[/SIZE]

if you change input "fruit" use Ctrl+Alt+F5 to refresh query table