Lookup find and match from one table to another table

[Saxon10]

Hi,

I have a two tables are Data and Report.

In data table the following columns are code, Type1, state1, Type2, state2 and report table are code and type.

In report table the both types in one column (Apple, Orange) but data table the type columns as two (separated Apple and Orange).

I would like to pick the state from data table into report table based on the code and type. In report table some time the type is blanks or XXX.

If type column is XXX then return X and if type column is Blanks then return No otherwise return state.

I am looking for New calculated column option to achieve my desired result.

Data:

CODE	TYPE1	STATE1	TYPE2	STATE2
X1	APPLE	M1	ORANGE	N1
X2	APPLE	M2	ORANGE	N2
X3	APPLE	M3	ORANGE	N3
X4	APPLE	M4	ORANGE	N4
X5	APPLE	M5	ORANGE	N5
X6	APPLE	M6	ORANGE	N6
X7	APPLE	M7	ORANGE	N7
X8	APPLE	M8	ORANGE	N8
X9	APPLE	M9	ORANGE	N9
X10	APPLE	M10	ORANGE	N10

Report

CODE	TYPE	STATE
X1	APPLE	M1
X1	APPLE	M1
X1	APPLE	M1
X1	ORANGE	N1
X1	ORANGE	N1
X1	ORANGE	N1
X1	ORANGE	N1
X1	ORANGE	N1
X1	XXX	X
X1	XXX	X
X1	XXX	X
X1	XXX	X
X1		NO
X1		NO
X1		NO
X2	APPLE	M2
X2	APPLE	M2
X2	APPLE	M2
X2	ORANGE	N2
X2	ORANGE	N2
X2	ORANGE	N2
X2	ORANGE	N2
X2	ORANGE	N2

[Fowmy · Accepted solution]

@Saxon10 

Where you see blank, either you can replace them with null in Power Query or modify the code as follows

NEW STATE = 
VAR TYPE1 = LOOKUPVALUE( Data[STATE1], Data[TYPE1] , Report[TYPE] , Data[CODE] , [CODE] )
VAR TYPE2 = LOOKUPVALUE( Data[STATE2], Data[TYPE2] , Report[TYPE] , Data[CODE] , [CODE] )
RETURN
IF(
    Report[TYPE])  = " " ,  "NO",
    COALESCE( TYPE1 , TYPE2 , "X" )
)

[smpa01 · Accepted solution]

@Saxon10  can you please try this

Column =
VAR _0 =
    DISTINCT (
        UNION (
            SUMMARIZE ( Data, Data[CODE], Data[STATE1], Data[TYPE1] ),
            SUMMARIZE ( Data, Data[CODE], Data[STATE2], Data[TYPE2] )
        )
    )
VAR _1 =
    MAXX (
        FILTER (
            _0,
            [CODE] = EARLIER ( Report[CODE] )
                && [TYPE1] = EARLIER ( Report[TYPE] )
        ),
        [STATE1]
    )
VAR _2 =
    CALCULATE ( MAX ( Report[TYPE] ) )
VAR _3 =
    SWITCH ( TRUE (), _2 = "XXX", "X", _2 = BLANK (), "NO", _1 )
RETURN
    _3

[Saxon10]

@smpa01 and @Fowmy

Thanks for your reply and help both solutions working well. 

[Fowmy]

@Saxon10 

You can add the following column to your table. 

NEW STATE = 
VAR TYPE1 = LOOKUPVALUE( Data[STATE1], Data[TYPE1] , Report[TYPE] , Data[CODE] , [CODE] )
VAR TYPE2 = LOOKUPVALUE( Data[STATE2], Data[TYPE2] , Report[TYPE] , Data[CODE] , [CODE] )
RETURN
IF(
    ISBLANK(Report[TYPE]) , "NO",
    COALESCE( TYPE1 , TYPE2 , "X" )
)

[Saxon10]

Thanks for your reply and help. I using extacly the same code but I am receving different result. Can you please advise why? My expect result is No but formula giving X. 

[Fowmy · Accepted solution]

@Saxon10 

Where you see blank, either you can replace them with null in Power Query or modify the code as follows

NEW STATE = 
VAR TYPE1 = LOOKUPVALUE( Data[STATE1], Data[TYPE1] , Report[TYPE] , Data[CODE] , [CODE] )
VAR TYPE2 = LOOKUPVALUE( Data[STATE2], Data[TYPE2] , Report[TYPE] , Data[CODE] , [CODE] )
RETURN
IF(
    Report[TYPE])  = " " ,  "NO",
    COALESCE( TYPE1 , TYPE2 , "X" )
)