IF state that applies to more than one record

[HorseRadish]

Hello All - 
I need some help. I have a software export for a number of computers and I want to create a custom column telling me if the Device has Winzip installed.  

From this list I have SN123 and SN126 with Winzip installed and each entry for SN123 and SN126 is marked True
I have each entry for SN124 and SN125 marked as False because they do not have Winzip installed. 

Desired output: 

DeviceName	ApplicationName	Winzip
SN123	Acrobat	TRUE
SN123	Zoom	TRUE
SN123	Webex	TRUE
SN123	Winzip	TRUE
SN124	Acrobat	FALSE
SN124	Zoom	FALSE
SN124	Webex	FALSE
SN125	Acrobat	FALSE
SN125	Zoom	FALSE
SN125	Webex	FALSE
SN126	Acrobat	TRUE
SN126	Zoom	TRUE
SN126	Webex	TRUE
SN126	Winzip	TRUE

I have been able to begin my process using Power Query, but I am not sure how to proceed from here. 

= Table.AddColumn(#"Filtered Rows1", "WinZip", each if Text.Contains([ApplicationName],"WinZip")
                                                    then "True"
                                                     else "False")

[HorseRadish]

Thank you everyone this is exactly what I needed. 

[Anonymous]

Hi @HorseRadish ,

Could you please tell me if your problem has been solved?

If so, please mark the reply as solution. More people will benefit from it.

Best Regards,

Stephen Tao

[serpiva64 · Accepted solution]

Hi,

i think it is possible to do it with less steps but it function:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvYzNDJW0lFyTC7KT0osUVCK1UEIRuXn56IIhKcmpVagimTmVWUWwIVMsBllgm6UCYZRptj0maLrM8XQZ4ZNnxm6PjOs+mBOjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DeviceName = _t, ApplicationName = _t]),
#"Grouped Rows" = Table.Group(Source, {"DeviceName"}, {{"AllRows", each _, type table [DeviceName=nullable text, ApplicationName=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddColumn([AllRows],"Winzip", each if Text.Contains([ApplicationName],"Winzip") then true else false)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"ApplicationName", "Winzip"}, {"ApplicationName", "Winzip"}),
#"Grouped Rows1" = Table.Group(#"Expanded Custom", {"DeviceName"}, {{"AllRows", each _, type table [DeviceName=nullable text, ApplicationName=text, Winzip=logical]}, {"Count", each List.Max([Winzip]), type logical}}),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows1", "AllRows", {"ApplicationName"}, {"ApplicationName"})
in
#"Expanded AllRows"

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

[Washivale]

Hi @HorseRadish :

go for a measure option instead of custom column

TestMeasure =

Var WinzipApp = COUNTAX(

    KEEPFILTERS(VALUES('Table'[DeviceName])),

    CALCULATE(COUNT('Table'[ApplicationName]), Filter(All('Table'[ApplicationName]) , 'Table'[ApplicationName] = "Winzip")

))

Return

IF(WinzipApp =1, TRUE())
use this measure in table visual alongwith rest of the columns.

Regards,
Washivale