LOOKUP with different columns in the same excel sheet

[Anonymous]

Hi,

I am new to power BI. I am trying to do lookup function for the below scenario.

File - 1

Column 1 - Product Category (Which has codes, blanks, #)

Column 2 - Big C

File - 2

Column 1 - Category Group Code

Column 2 - Market Code

Column 3 - Category Code

Column 4 - Division Code

Column 5 - Sub division 2 Code

Column 6 - Big C

What i need is

I need Big C in File 1 by comparing the product category column of File 1 with Columns 1 to 5 in File 2 and bring in Big C which is there in File 2.

Also note that, every month the File 2 data changes and its not constant

In excel - we do based on Iferrer and V-lookup. when i try in power bi, it is giving me an error.

Please help

Thanks in advance!

Regards,

Vaishnavi

[dax]

Hi Vaishu0508,

You could try to create relationship between two sheets, then add column from two sheets in same table to see whetehr it work or not. Or you also could use merge query by M code to see whether it work or not. By the way, if possible, could you please inform me detailed information (such as your sample data and your expected output)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

Please find below.

Table 1

PCAT             Big C

CF3000

CF1315

CF1327

Table 2

Division Code        Category Code            Market Code               Big C

CF3000                  CF1315                        CF1327                       Home Care

Regards,

Vaishnavi 

[dax · Accepted solution]

Hi Vaishu0508,

You could try to unpivot table 2, then merge table 1 and table two, you could refer to below M code 

Table 1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnYzNjAwUNJRUorVAfEMjQ1NkXlG5giegYERgmOKrMvMEMKJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"PCAT " = _t, #"Big C" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PCAT ", type text}, {"Big C", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"PCAT "}, t2, {"Value"}, "t2", JoinKind.LeftOuter),
    #"Expanded t2" = Table.ExpandTableColumn(#"Merged Queries", "t2", {"Big C"}, {"t2.Big C"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded t2",{"Big C"})
in
    #"Removed Columns"

Table 2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnYzNjAwUNIBMgyNDU2hDCNzIMMjPzdVwTmxKFUpVgek0ASqzsjAEEwbGBiBVLkpxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Division Code" = _t, #"Category Code" = _t, #"Market Code" = _t, #"Big C" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Division Code", type text}, {"Category Code", type text}, {"Market Code", type text}, {"Big C", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Big C"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you so much.

I have done the Unpivot and lookup.

Regards,

Vaishnavi

[kentyler]

You could do a lookup in dax, looking up each column from file 2 in the product category field of file 1. You would store the results in VAR statements in your measure.

Then you would use a SWITCH statement to see  which VAR had been found, and the SWITCH would return the big C if one of the VARs was true.
you would probably use this function 

LOOKUPVALUE

• 03/12/2019
• 2 minutes to read
•  

Returns the value in result_columnName for the row that meets all criteria specified by search_columnName and search_value.

Syntax

DAXCopy

LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…[, <alternateResult>])   

Parameters

Term Definition

result_columnName	The name of an existing column that contains the value you want to return. The column must be named using standard DAX syntax, usually, fully qualified. It cannot be an expression.
search_columnName	The name of an existing column, in the same table as result_columnName or in a related table, over which the look-up is performed. The column must be named using standard DAX syntax, usually, fully qualified. It cannot be an expression.
search_value	A scalar expression that does not refer to any column in the same table being searched.
alternateResult	(Optional) The value returned when the context for result_columnName has been filtered down to zero or more than one distinct value. When not provided, the function returns BLANK() when result_columnName is filtered down to zero value or an error when more than one distinct value.

and here is SWITCH

Evaluates an expression against a list of values and returns one of multiple possible result expressions.

Syntax

DAXCopy

SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])  

Parameters

Term Definition

expression	Any DAX expression that returns a single scalar value, where the expression is to be evaluated multiple times (for each row/context).
value	A constant value to be matched with the results of expression.
result	Any scalar expression to be evaluated if the results of expression match the corresponding value.
else	Any scalar expression to be evaluated if the result of expression doesn't match any of the value arguments.

[Mariusz]

Hi @Anonymous 

Can you create a sample for both tables?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Anonymous]

Hi,

I have posted a sample in the same post as reply as to another person. Please check

Regards,

Vaishnavi