Reply
HorseRadish
Frequent Visitor
Partially syndicated - Outbound

IF state that applies to more than one record

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: 

DeviceNameApplicationNameWinzip
SN123Acrobat TRUE
SN123ZoomTRUE
SN123WebexTRUE
SN123WinzipTRUE
SN124Acrobat FALSE
SN124ZoomFALSE
SN124WebexFALSE
SN125Acrobat FALSE
SN125ZoomFALSE
SN125WebexFALSE
SN126Acrobat TRUE
SN126ZoomTRUE
SN126WebexTRUE
SN126WinzipTRUE


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")

 

 

 



 

1 ACCEPTED SOLUTION
serpiva64
Solution Sage
Solution Sage

Syndicated - Outbound

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"

serpiva64_0-1665611535334.png

 

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 !

View solution in original post

4 REPLIES 4
HorseRadish
Frequent Visitor

Syndicated - Outbound

Thank you everyone this is exactly what I needed. 

v-stephen-msft
Community Support
Community Support

Syndicated - Outbound

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
Solution Sage
Solution Sage

Syndicated - Outbound

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"

serpiva64_0-1665611535334.png

 

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
Resolver V
Resolver V

Syndicated - Outbound

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
avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)