Tried Lookup But Error "a table of multiple values was supplied where a single value was expected"

[Anonymous]

Hi,

Below is the Data and I was trying to get the Manager Territory and  True / False as shown in the below Table:

Requirements are:

1. Manager Territory should get populated by Looking the Manager ID in Column "ID" and get the respective Territory ID from Column "Territory Id"

2. Now the Calculated Column "Manager ID" should be matched with column "Parent Territory Id" to flag the true or false. We are doing this since there is a possibility that the Parent Territory ID is wrong mapped for ID. 

For example: ID = 3, his Manage is 7 and Parent Territory id is A005. But the correct parent territory id is A006.

Note: If we are not able to get the Manager ID column, we can ignore it. But we need the Flagging column, whether it is True or False.

Challenges:

One ID (can be manager as well) can have 2 or more territories. So it should be true if the Parent Territory ID falls in any of the Manager ID of the respective Manager.

Thanks a lot in advance.

Data:

Id	Name	Territory Id	Parent Territory Id	Manager ID	Manager Name	Manager Territory (Calculated Field)	True / False (Calculated Field)
1	A	A001	A005	5	EE		TRUE
2	B	A002	A005	6	FF		TRUE
3	C	A003	A005	7	GG		FALSE
3	C	A004	A007	8	GG		TRUE
4	D	A003	A009	9	II		FALSE
5	EE	A005	A0099	100	JJJ
6	FF	A005	A0055	55	EEE
7	GG	A006	A0055	66	FFF
8	GG	A007	A0088	99	III
9	II	A008	A0099	100	JJJ
10	JJ	A009	A0055	55	EEE

[lbendlin]

@Anonymous You didn't specify if you want this in DAX or Power Query.  Here's a Power Query version.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY87C8IwFIX/SsncIWntw1HtgxQUB7fSIWAQByuELv577yOYWHG4Ock9Xy7njqPQV5GKk3lYkIt17r483Suh7tk4Oy/Jqns0s7lZl+gmetCAKR2FguYOS0rFUoBgtS0BGVz37GQBKKG6joAcrgd28gBUUH2/BjYsaNYBwG7zNWELgqU1AT7OZzgSaCsp4RyGgSifKaYKWoa/8zo+F7llBJX8m1eqI6hiqbG35Uwcyudj928oxY+w1m+q6Q0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Manager Territory", each try (Table.SelectRows(#"Promoted Headers",(k)=> k[Id]=[Manager ID]))[Territory Id]{0} otherwise null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Matching", each [Parent Territory Id]=[Manager Territory])
in
    #"Added Custom1"

How to use this code: Create a new Blank Query, then click on "Advanced Editor", and then replace the code in the window with the code provided here. Then click "Done".