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.
Hi DataLife321,
I'd like to help you but provide sample data (as table) and also expected result please.
| 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" & " " &
DataLife321, I'm sorry but I don't get the logic.
How you can achieve this if all column values are the same and there is no connection between Customer (Company1, Company2 and Company3) vs CompanySA, CompanyMS and CompanyFE
Are you sure that you provided correct sample data?
Try to explan as simple as possible please.
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