Power BI Excel Index Match help needed

[jwelch176]

New user here.  I have two tables in my Power BI Report.  Table 1 is a contact list with addresses.

Name	Address1	Address2	City	State	Zip
Tom Jones	123 Sesame St		Beaverton	OR	97003

Table 2 has zip codes and their driving distances to four different locations.  

Zip Code	Alice's house	Barbara's house	Charlie's house	Derrick's House	Closest Location -  Distance	Closest Location -  Name
97002	17.77	13.352	24.659	26.048	13.352	Barbara's house
97003	6.517	11.827	9.336	18.688	6.517	Alice's house
97004	30.61	26.192	39.904	19.738	19.738	Derrick's House
97005	5.144	8.768	11.183	14.236	5.144	Alice's house
97006	7.297	12.17	10.755	17.872	7.297	Alice's house
97007	7.033	11.89	7.179	21.138	7.033	Alice's house
97008	0	6.328	12.705	17.435	0	Alice's house

I need to add a column to the address list that will look at the contact's zip code; do an index match function; and then bring back the data in the Closest Location -  Name field.

[v-jayw-msft · Accepted solution]

Hi @jwelch176 ,

In Query Editor, select Zip Code, Closest Location - Distance, Closest Location - Name and use unpivot other columns feature.

Result:

Then use merge query feature.

Expand the table and remove duplicate.

Best Regards,

Jay

[Greg_Deckler]

@jwelch176 - I may be mistaken, but I would almost certainly unpivot those distance columns. Then it should be a cakewalk. Otherwise, maybe something like MC Aggregations (MIN) - https://community.powerbi.com/t5/Quick-Measures-Gallery/Multi-Column-Aggregations-MC-Aggregations/m-...

[Greg_Deckler]

@jwelch176 Did that work?