Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
HorseRadish
Frequent Visitor

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
Super User
Super User

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

Thank you everyone this is exactly what I needed. 

v-stephen-msft
Community Support
Community Support

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
Super User
Super User

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors