SUMMARIZE table data, DISTINCT and enter data manually

[Rene_E]

Hello all,

I have this table populated with data.

In this table are computers with their Operating System versions.
What I want to achieve is to create a new table "OS Version Mapping" with all unique values of column OSVersionCheck filtered on operatingSystem which need to contain the string "Windows". Additionally, I want to add a manual column called "End of Support Date" where I will manually enter the end of support date for that unique OS version.

I tried the following but got only unique rows. 

OS Version Mapping = FILTER(SUMMARIZE('AD - combined','AD - combined'[OSversionCheck],'AD - combined'[operatingSystem]),CONTAINSSTRING('AD - combined'[operatingSystem],"Windows"))

Adding a DISTINCT to this doesn't work.

OS Version Mapping = DISTINCT(FILTER(SUMMARIZE('AD - combined','AD - combined'[OSversionCheck],'AD - combined'[operatingSystem]),CONTAINSSTRING('AD - combined'[operatingSystem],"Windows")))

OS Version Mapping = 
var t = (FILTER(SUMMARIZE('AD - combined','AD - combined'[OSversionCheck],'AD - combined'[operatingSystem]),CONTAINSSTRING('AD - combined'[operatingSystem],"Windows")))
return
DISTINCT('t'[OSversionCheck]) <---- This is not possible with a temp table...

It also doesn't seem to be possible to add a new column to an existing table and add manual data.
In which case I think the only option is to create a new table with OSVersionCheck and End of Support Date data entered manually.

[amitchandak · Accepted solution]

@Rene_E , Try like

OS Version Mapping =SUMMARIZE( FILTER('AD - combined','AD - combined', CONTAINSSTRING('AD - combined'[operatingSystem],"Windows") ), 'AD - combined'[OSversionCheck],'AD - combined'[operatingSystem])

You can add manual data in formula only , you have check for every row value and do that

example

New column  = Switch( True() ,

[Varsion] , "10.0", date(2022,12,31),

[Varsion] , "11.0", date(2026,12,31),

// add others

date(2020,12,31)

)

[Rene_E]

I was not able to get unique OSVersionCheck values. So I ended up recreating the custom table "OS Version Mapping" with only DISTINCT OSVersionCheck values and then created both End of Support Date and operatingSystem as new column with manual data.

[amitchandak · Accepted solution]

@Rene_E , Try like

OS Version Mapping =SUMMARIZE( FILTER('AD - combined','AD - combined', CONTAINSSTRING('AD - combined'[operatingSystem],"Windows") ), 'AD - combined'[OSversionCheck],'AD - combined'[operatingSystem])

You can add manual data in formula only , you have check for every row value and do that

example

New column  = Switch( True() ,

[Varsion] , "10.0", date(2022,12,31),

[Varsion] , "11.0", date(2026,12,31),

// add others

date(2020,12,31)

)

[Rene_E · Accepted solution]

Hi @amitchandak,

I was able to populate the End of Support Date with your help. I only needed to remove "true()" from your formula. Thanks for that.

End of Support Date = switch(
   'OS Version Mapping'[OSversionCheck], 
      "10.0 (19042) Enterprise", date(2022,12,21),
      "10.0 (18363) Enterprise", date(2021,12,21)
)

The first formula to summarize data and only keep the unique OSVersionCheck values didn't work. It was the same output I had with unique rows.

[amitchandak]

@Rene_E , You can use addcolumns on same summarize script

or

create that var table and then use add the column.

You got what you want, of still there is some issue?