Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

LOOKUP with different columns in the same excel sheet

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...
Need Help
  • dax's avatar
    dax
    6 years ago

    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.

     

     

kentyler's avatar
kentyler
Icon for Solution Sage rankSolution Sage
6 years ago

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_columnNameThe 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_columnNameThe 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_valueA 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
expressionAny DAX expression that returns a single scalar value, where the expression is to be evaluated multiple times (for each row/context).
valueA constant value to be matched with the results of expression.
resultAny scalar expression to be evaluated if the results of expression match the corresponding value.
elseAny scalar expression to be evaluated if the result of expression doesn't match any of the value arguments.