Lookup value function to return value is not blank

[Anonymous]

Dear all,

May I ask your advice on below issue?

I want to apply look up value to find a value from a column in Power BI Desktop. However, in the result column have 2 value: blank or # value. So I want that the formula only pickup the # value from result column. Below is example of data. Thanks for your support!

Dataset 1

Product	Safety time_Plant A	Safety time_Plant B	Safety time_Plant C
Table	5
Table		4
Table			3
Chair	2
Chair		1
Chair			3

Dataset 2: => Expected to lookup column safety time from Dataset 1

Plant	Product	Safety time
A	Table	5
A	Table	5
B	Chair	4
C	Chair	3

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

Please select all of the column with the prefix "Safety time_Plant" and unpivot these columns in Power Query Editor as suggested by @amitchandak .  

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCklMyklV0lEyBWIQitVBiAGRCRYxIDIGizlnJGYWAXlGSHphYkBkiEUMojcWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Safety time_Plant A" = _t, #"Safety time_Plant B" = _t, #"Safety time_Plant C" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Safety time_Plant A", Int64.Type}, {"Safety time_Plant B", Int64.Type}, {"Safety time_Plant C", Int64.Type}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Safety time_Plant A", "Safety time_Plant B", "Safety time_Plant C"}, "Plant", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Only Selected Columns","Safety time_Plant","",Replacer.ReplaceText,{"Plant"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Product", "Plant"}, {{"Safety time", each List.Sum([Value]), type number}})
in
    #"Grouped Rows"

But the returned Safety Time is different with yours, could you please provide the calculation logic of Safety Time? How did you get them(5,5,4,3) base on the data from Dataset 1?

Best Regards

[amitchandak]

@Anonymous , if you only have 3 such column

New column =
var _A = maxx(filter(Data1,Date1[Product] =Data2[Product]), [Safety time_Plant A])
var _B = maxx(filter(Data1,Date1[Product] =Data2[Product]), [Safety time_Plant B])
var _C = maxx(filter(Data1,Date1[Product] =Data2[Product]), [Safety time_Plant C])

return
Switch([Plant],
"A", _A ,
"B", _B ,
"C", _C
)

If need use min in place of Max

If there more then 3 such column, Then unpivot the first table

https://radacad.com/pivot-and-unpivot-with-power-bi

And add the filter of plant