Forum Discussion

ulyssus's avatar
ulyssus
Regular Visitor
3 years ago

Help - lookup value

Hi All, 

 

I am new to power bi and couldn't figure this query out. I have a table like below with the list of work email based on the source. 

NameWork emailSource
Dave[email protected]A
Bell[email protected]C
White[email protected]B

 

Something like this

 

NameABC
Dave[email protected]  
Bell [email protected] 
white  [email protected]

 

And then compare the results to see if the email in column "A" matches the email in Column 'B' and also next match with column "c". When i try to do it, the blanks also counts as a value which i don't want. I just need to identify the emails which exist in sources B & C when compared with column "A".

 

The intent here is to identify email id's which matches the source (B & C) when compared with source A and return the email id that matches and ignore the blanks.

 

Can someone pls help? I couldn't figure this out.

 

Thanks 

Need Help

2 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago

    not really get your point, just try to transform the format

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksS1XSUUp0SExK1kvOzwWyHZVidaKVnFJzcoCcJCQJZ7BEeEZmCUhLMpKMk1JsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Work email" = _t, Source = _t]),
    Custom1 = Table.FromRecords(Table.TransformRows(Source,each Record.AddField([[Name]],[Source],[Work email])),{"Name"}&List.Distinct(Source[Source]),2)
in
    Custom1
  • ulyssus's avatar
    ulyssus
    Regular Visitor
    3 years ago

    Hi, Thanks for the response. I was able to transform the data from rows to columns. now I need to check if the email in column b & C matches with column A. if so, i would like to return the same value (it should ignore the blanks as it comes up as "not matched").