Forum Discussion
Lookup value function to return value is not blank
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 |
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
2 Replies
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
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
