Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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")
Solved! Go to 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 !
Thank you everyone this is exactly what I needed.
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
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 !
Hi @HorseRadish :
go for a measure option instead of custom column
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |