Help - lookup value

[ulyssus]

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. 

Name	Work email	Source
Dave	a@abc.com	A
Bell	b@abc.com	C
White	c@abc.com	B

Something like this

Name	A	B	C
Dave	a@abc.com
Bell		b@abc.com
white			c@abc.com

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 

[ulyssus]

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").

[wdx223_Daniel]

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