Forum Discussion
IF Statement to return on same row depending on condition
Hi DataLife321
You can try the following code in advanced editor in power query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLUjMqzRU0lGKRMGxOnBJIzRJZDljZHE0fSbI4mhypsjiYLlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Column1 = _t, CompanySA = _t, CompanyMS = _t, CompanyFE = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Column1", type text}, {"CompanySA", type text}, {"CompanyMS", type text}, {"CompanyFE", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let a=if [CompanySA]="Y" then "CompanySA" else "", b=if [CompanyMS]="Y" then "CompanyMS" else "", c=if [CompanyFE]="Y" then "CompanyFE" else "" in if [Customer]="Company1"and [Column1]="Y" and a<>"" then a&" "&b&" "&c else if [Customer]<>"Company1"and [Column1]="Y" and a<>"" then a&" "&b else null) in #"Added Custom"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
| Customer | Column1 | CompanySA | CompanyMS | CompanyFE | CustomColumn Expectation |
| Company1 | Y | Y | Y | Y | CompanySA - CompanyFE - CompanyMS |
| Company2 | Y | Y | Y | Y | CompanySA - CompanyMS |
| Company3 | Y | Y | Y | Y |
Expected Result should be shown above in the table - (I did this in a new column column measure in the Data view but prefer a custom column as I need more IF's) Happy to keep going if you can provide me a way to do it this way.
'Master Sheet - (Doc)'[Column1] = "y" &&'Master Sheet - (Doc)'[Customer] = "Company1" &&'Master Sheet - ( Doc)'[CompanySA] <> BLANK(),"CompanySA" & " " &
Hi DataLife321
You can try the following code in advanced editor in power query.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLUjMqzRU0lGKRMGxOnBJIzRJZDljZHE0fSbI4mhypsjiYLlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Column1 = _t, CompanySA = _t, CompanyMS = _t, CompanyFE = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Column1", type text}, {"CompanySA", type text}, {"CompanyMS", type text}, {"CompanyFE", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let
a=if [CompanySA]="Y" then "CompanySA" else "",
b=if [CompanyMS]="Y" then "CompanyMS" else "",
c=if [CompanyFE]="Y" then "CompanyFE" else ""
in if [Customer]="Company1"and [Column1]="Y" and a<>"" then a&" "&b&" "&c else if [Customer]<>"Company1"and [Column1]="Y" and a<>"" then a&" "&b else null)
in
#"Added Custom"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Great stuff mate thank you.. Managed to use this to get it working appreciate it