Forum Discussion
Return values from some cells if other cells meet criteria
I am trying to return a list of computer names and possibly Windows build version and memory available if build version is less than X and memory is less than X. So ideally on Sheet 1 (Overv...
With some assumptions (builds are numbers, etc) you may use Power Query
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Removed Other Columns" = Table.SelectColumns(
Source,
{"Device Name", "Build", "Memory"}),
#"Changed Type" = Table.TransformColumnTypes(
#"Removed Other Columns",
{
{"Device Name", type text},
{"Build", Int64.Type},
{"Memory", Int64.Type}
}),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Action",
each
if [Build] < 19042
then
if [Memory] < 15000
then "Replace"
else "Reimage"
else "Nothing to do")
in
#"Added Custom"Results is
So I want to display data ONLY if the Memory and Build #'s meet the criteria.
So I don't want any "Nothing to do" columns, I only want "Replace" or "Reimage".
That's simple filter in Power Query transformation
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Removed Other Columns" = Table.SelectColumns( Source, {"Device Name", "Build", "Memory"}), #"Changed Type" = Table.TransformColumnTypes( #"Removed Other Columns", { {"Device Name", type text}, {"Build", Int64.Type}, {"Memory", Int64.Type} }), #"Added Custom" = Table.AddColumn( #"Changed Type", "Action", each if [Build] < 19042 then if [Memory] < 15000 then "Replace" else "Reimage" else "Nothing to do"), #"Filtered Rows" = Table.SelectRows( #"Added Custom", each ([Action] <> "Nothing to do")) in #"Filtered Rows"it gives
