Nov 19 2021 09:56 AM
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 (Overview) I would show the following:
So if the computer has less than say 15 gig of ram AND has older than Build # 19042 build number of Windows we would return "Replace" on it.**
If the computer has over 15 gigs of ram and the Build # is older than 19042 we would say "Reimage"
Nov 19 2021 11:51 AM
Nov 19 2021 11:53 AM
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
Nov 19 2021 11:56 AM
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".
Nov 19 2021 11:59 AM
Nov 19 2021 12:20 PM
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
Nov 19 2021 12:45 PM
What should the formula return if the build is 19042 or higher? Reimage? If so:
IF(AND(C4<19042,D4<15*1024),"Replace","Reimage")